Horton

Re: speeding up planning with partitions

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

View thread View on postgr.es
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>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-03-01T13:01:31Z
Lists: pgsql-hackers

Attachments

Imai-san,

Thanks for testing and sorry it took me a while to reply.

On 2019/02/25 15:24, Imai, Yoshikazu wrote:
> [update_pt_with_joining_another_pt.sql]
> update rt set c = jrt.c + 100 from jrt where rt.b = jrt.b;
> 
> [pgbench]
> pgbench -n -f update_pt_with_joining_another_pt_for_ptkey.sql -T 60 postgres
> 
> [results]
> (part_num_rt, part_num_jrt)  master  patched(0001)
> ---------------------------  ------  -------------
>                   (3, 1024)    8.06           5.89
>                   (3, 2048)    1.52           0.87
>                   (6, 1024)    4.11           1.77
> 
> 
> 
> With HEAD, we add target inheritance and source inheritance to parse->rtable in inheritance_planner and copy and adjust it for child tables at beginning of each planning of child tables.
> 
> With the 0001 patch, we add target inheritance to parse->rtable in inheritance_planner and add source inheritance to parse->rtable in make_one_rel(under grouping_planner()) during each planning of child tables.
> Adding source inheritance to parse->rtable may be the same process between each planning of child tables and it might be useless. OTOH, from the POV of making inheritance-expansion-at-bottom better, expanding source inheritance in make_one_rel seems correct design to me.
> 
> How should we do that...?

To solve this problem, I ended up teaching inheritance_planner to reuse
the objects for *source* inheritance child relations (RTEs,
AppendRelInfos, and PlanRowMarks) created during the planning of the 1st
child query for the planning of subsequent child queries.  Set of source
child relations don't change between different planning runs, so it's okay
to do so.  Of course, I had to make sure that query_planner (which is not
in the charge of adding source inheritance child objects) can notice that.

Please find attached updated patches.  Will update source code comments,
commit message and perform other fine-tuning over the weekend if possible.

Thanks,
Amit

Commits

  1. Clean up handling of constraint_exclusion and enable_partition_pruning.

  2. Add test case exercising formerly-unreached code in inheritance_planner.

  3. Speed up planning when partitions can be pruned at plan time.

  4. Avoid crash in partitionwise join planning under GEQO.

  5. Avoid passing query tlist around separately from root->processed_tlist.

  6. Build "other rels" of appendrel baserels in a separate step.

  7. Get rid of duplicate child RTE for a partitioned table.

  8. Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.

  9. Don't copy PartitionBoundInfo in set_relation_partition_info.

  10. Move building of child base quals out into a new function

  11. Reorganize planner code moved in b60c39759908

  12. Move inheritance expansion code into its own file

  13. Fix inherited UPDATE/DELETE with UNION ALL subqueries.

  14. Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.