I'm Yuzuko Hosoya. This is my first PostgreSQL project participation.
I have been developing partitioning advisor prototype with Julien Rouhaud. It will be a new feature of HypoPG[1], which is a PostgreSQL extension, and will help partitioning design tuning. Currently, HypoPG only supports index design tuning; it allows users to define hypothetical indexes for real tables and shows resulting queries' plan/cost with EXPLAIN as if they were actually constructed. Since declarative partitioning will be greatly improved in PostgreSQL 11 and further versions, there are emerging needs to support partitioning design tuning. This is why we are working on partitioning advisor. We plan to release the first version of partitioning advisor for PostgreSQL 11, and then, improve it for PostgreSQL 12.
Interesting.
- Estimating stats It is complicated because hypothetical partition has no data. Currently, we compute hypothetical partition's size using clauselist_selectivity() according to their partition bound and original table stats. As a result, estimate is done with low accuracy, especially if there is WHERE clause. We will improve during developing, but for now, we don't have good ideas.
I haven't yet read the patch but curious to know. Suppose we have table which is already loaded with some data. Now, if I create hypothetical partitions on that will we create any stat data (mcv, histogram) for hypothetical table? because, in this case we already have the data from the main table and we also have partition boundary for the hypothetical table. I am not sure you are already doing this or its an open item?