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.

+10 for this feature. Finding that the partitioning doesn't work as
expected after implementing is rather painful since the partitioning
and unpartitioning is time consuming right now.

>
>
> Overview of partitioning advisor
> ---------------------------------------
> - Partitioning advisor allows users to define multiple hypothetical partitioning
> schemes on real tables and real data
> - PostgreSQL can show resulting queries' plan/cost with EXPLAIN using hypothetical
> partitioning schemes
> Users can quickly check how their queries would behave if some tables were
> partitioned, and try different partitioning schemes (for instance, to optimize some
> queries efficiency Vs. maintenance efficiency).
>
>
> Partitioning advisor works as follows:
>
> Usage
> ---------
> 0. Consider this target table, t1
> #= CREATE TABLE t1 (a int, b text);
> #= INSERT INTO t1 SELECT i, 'test' FROM generate_series(1,299) i ;
> #= EXPLAIN SELECT * FROM t1;
> QUERY PLAN
> -----------------------------------------------------
> Seq Scan on t1 (cost=0.00..4.99 rows=299 width=9)
> (1 row)
>
> 1. Partition the target table hypothetically
> #= SELECT * FROM hypopg_partition_table('t1','partition by range(a)');
> The hypopg_partition_table() defines hypothetical range partitioned table 't1'
> by the partition key 'a' and stores these information into backend local memory.
>
> 2. Create hypothetical partitions
> #= SELECT * FROM hypopg_add_partition('t1_1','partition of t1 for values from (1) to (100)');
> #= SELECT * FROM hypopg_add_partition('t1_2','partition of t1 for values from (100) to (300)');
> The hypopg_add_partition() defines hypothetical partitions t1_1 and t1_2 according
> to their bounds 'from (1) to (100)' and 'from (100) to (300)' respectively, and stores
> these information into backend local memory.
>
> 3. PostgreSQL can show resulting queries' plan/cost with EXPLAIN
> #= EXPLAIN SELECT * FROM t1;
> QUERY PLAN
> ---------------------------------------------------------------
> Append (cost=0.00..7.49 rows=299 width=9)
> -> Seq Scan on t1 t1_1 (cost=0.00..1.99 rows=99 width=9)
> -> Seq Scan on t1 t1_2 (cost=0.00..4.00 rows=200 width=9)
> (3 rows)
> PostgreSQL retrieves hypothetical partitioning schemes from HypoPG.
> And then if the referred table is defined as hypothetical partitioned table,
> PostgreSQL creates plans using them.
>
> This is a simple example. In addition, it enables us to simulate range/list/hash
> partitioning, partition pruning, N-way join and partition-wise join/aggregation.
> It is already helpful for users to design partitioning schemes.
>
>
> Current implementation
> ------------------------
> We mainly use get_relation_info_hook(). What we do in this hook is to inject
> hypothetical partitioning schemes according to user definition. At first, we do
> all processes that are done at expand_inherited_tables(). Specifically, we
> expand root->simple_rte_array and root->parse->rtable, rewrite target
> table's RangeTblEntry as a partitioned table, and create RangeTblEntries and
> AppendRelInfos for all hypothetical partitions. Besides that, we set hypothetical
> partition's name into rte->alias->aliasname at this time to display hypothetical
> partition's name with EXPLAIN. And then, we rewrite RelOptInfo as needed.
> Specifically, we add partition information, which is set at set_relation_partition_info(),
> to hypothetical partitioned tables, and set rel->tuples and rel->pages for
> hypothetical partitions.
>
>
> However, PostgreSQL isn't designed to have hypothetical tables,

I agree. But there are light-weight tables like foreign tables, views
and partitioned tables themselves. These kinds of tables do not have
any storage associated with them. We could implement semi-hypothetical
partitioned table using these three. The reason I say it's
semi-hypothetical since we will need to create some real objects, but
which do not take actual storage. The idea is to create partitioned
table with foreign table partitions which point to views simulating
partitions. The steps are
1. Create views one per partition which select data from the
unpartitioned table that would fall in a partition simulated by that
view. So something like SELECT * FROM unpartitioned_table WHERE
partition constraint for that partition.
2. Create partitioned table
3. Create foreign table partitions that point to the views created in
the first step.
4. ANALYZE the foreign tables and the partitioned table

Now if we EXPLAIN the query on unpartitioned table by redirecting it
to the partitioned table, we would get the EXPLAIN plans as if the
query is running on the partitioned table. We will need to zero out
the FDW costs, so that the cost of accessing foreign table comes out
to be same as accessing a local table. That's mostly setting the right
FDW GUCs.

Since we are creating and dropping some real objects, may be we want
to create temporary objects (we don't have support to create temporary
foreign tables for now, but may be that's desirable feature) or create
them in a different database to reduce catalog bloat. Similarly we
won't be able to create indexes on the foreign table, but may be we
could simulate those using hypothetical indexes feature.

This method doesn't need any core changes which are useful only for
this extension. Supporting temporary foreign table and declarative
indexes on foreign tables may be seen as separate features and
acceptable in the community.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company