Re: speeding up planning with partitions

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>

To: Tom Lane <tgl@sss.pgh.pa.us>

Cc: Imai Yoshikazu <yoshikazu_i443@live.jp>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>, "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>, "'Amit Langote'" <amitlangote09@gmail.com>, David Rowley <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@postgresql.org>

Date: 2019-03-26T07:16:41Z

Lists: pgsql-hackers

Attachments

v36-0001-Build-other-rels-of-appendrel-baserels-in-a-sepa.patch (text/plain) patch v36-0001
v36-0002-Get-rid-of-duplicate-child-RTE-for-partitioned-t.patch (text/plain) patch v36-0002
v36-0003-Delay-adding-inheritance-child-tables-until-quer.patch (text/plain) patch v36-0003
v36-0004-Perform-pruning-in-expand_partitioned_rtentry.patch (text/plain) patch v36-0004
v36-0005-Teach-planner-to-only-process-unpruned-partition.patch (text/plain) patch v36-0005
v36-0006-Fix-inheritance_planner-to-avoid-useless-work.patch (text/plain) patch v36-0006
v36-0007-Adjust-inheritance_planner-to-reuse-source-child.patch (text/plain) patch v36-0007

On 2019/03/25 20:34, Amit Langote wrote:
> Performance loss for smaller number of partitions is in the noise range,
> but what we gain for large number of partitions seems pretty significant
> to me:

I didn't specify the benchmark setup instructions:

partitioned table creation (N: 2...8192):

create table rt (a int, b int, c int) partition by range (a);
select 'create table rt' || x::text || ' partition of rt for values from
(' || (x)::text || ') to (' || (x+1)::text || ');' from generate_series(1,
N) x;
\gexec

select.sql:

\set param random(1, N)
select * from rt where a = :param;

pgbench -n -T 120 -f select.sql

> nparts    no live_parts    live_parts
> ======    =============    ==========
> 2	           3397          3391
> 8	           3365          3337
> 32	           3316          3379
> 128	           3338          3399
> 512	           3273          3321
> 1024	           3439          3517
> 4096	           3113          3227
> 8192	           2849          3215
> 
> Attached find updated patches.

Rebased patches attached.

Thanks,
Amit

Commits

Clean up handling of constraint_exclusion and enable_partition_pruning.
- e03ff739695c 12.0 landed
- 11ea45ffec9a 11.3 landed
Add test case exercising formerly-unreached code in inheritance_planner.
- 8fba397f0ca7 12.0 landed
Speed up planning when partitions can be pruned at plan time.
- 428b260f87e8 12.0 landed
Avoid crash in partitionwise join planning under GEQO.
- 7ad6498fd5a6 12.0 landed
- d70c147fa217 11.3 landed
Avoid passing query tlist around separately from root->processed_tlist.
- 333ed246c6f3 12.0 landed
Build "other rels" of appendrel baserels in a separate step.
- 53bcf5e3dbea 12.0 landed
Get rid of duplicate child RTE for a partitioned table.
- e8d5dd6be79e 12.0 landed
Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.
- 734308a22072 12.0 landed
Don't copy PartitionBoundInfo in set_relation_partition_info.
- c8151e642368 12.0 landed
Move building of child base quals out into a new function
- 80579f9bb171 12.0 landed
Reorganize planner code moved in b60c39759908
- d723f56872a9 12.0 landed
Move inheritance expansion code into its own file
- b60c39759908 12.0 landed
Fix inherited UPDATE/DELETE with UNION ALL subqueries.
- c03ad5602f52 9.4.0 cited
Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.
- b3aaf9081a1a 9.2.0 cited