Re: Proposal: Partitioning Advisor for PostgreSQL

On Thu, May 24, 2018 at 4:16 PM, Yuzuko Hosoya <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> Hello,
>
> 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?

>
>
> [1] https://github.com/HypoPG/hypopg
>
> Best regards,
> ----
> Yuzuko Hosoya
> NTT Open Source Software Center
>
>
>
>

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com