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: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>

Cc: "'Amit Langote'" <amitlangote09@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@postgresql.org>

Date: 2019-03-08T09:18:00Z

Lists: pgsql-hackers

Attachments

v29-0001-Build-other-rels-of-appendrel-baserels-in-a-sepa.patch (text/plain) patch v29-0001
v29-0002-Delay-adding-inheritance-child-tables-until-quer.patch (text/plain) patch v29-0002
v29-0003-Adjust-inheritance_planner-to-reuse-source-child.patch (text/plain) patch v29-0003
v29-0004-Further-tweak-inheritance_planner-to-avoid-usele.patch (text/plain) patch v29-0004
v29-0005-Perform-pruning-in-expand_partitioned_rtentry.patch (text/plain) patch v29-0005
v29-0006-Teach-planner-to-only-process-unpruned-partition.patch (text/plain) patch v29-0006
v29-0007-Don-t-copy-PartitionBoundInfo-in-set_relation_pa.patch (text/plain) patch v29-0007

On 2019/03/08 16:16, Imai, Yoshikazu wrote:
> So I modified the code and did test to confirm memory increasement don't happen. The test and results are below.
> 
> [test]
> * Create partitioned table with 1536 partitions.
> * Execute "update rt set a = random();"
> 
> [results]
> A backend uses below amount of memory in update transaction:
> 
> HEAD: 807MB
> With v26-0001, 0002: 790MB
> With v26-0001, 0002, 0003: 860MB
> With v26-0003 modified: 790MB

Can you measure with v28, or better attached v29 (about which more below)?

> I attached the diff of modification for v26-0003 patch which also contains some refactoring.
> Please see if it is ok.

I like the is_first_child variable which somewhat improves readability, so
updated the patch to use it.

Maybe you know that range_table_mutator() spends quite a long time if
there are many target children, but I realized there's no need for
range_table_mutator() to copy/mutate child target RTEs.  First, there's
nothing to translate in their case.  Second, copying them is not necessary
too, because they're not modified across different planning cycles.  If we
pass to adjust_appendrel_attrs only the RTEs in the original range table
(that is, before any child target RTEs were added), then
range_table_mutator() has to do significantly less work and allocates lot
less memory than before.  I've broken this change into its own patch; see
patch 0004.

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