Horton

Re: speeding up planning with partitions

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

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Amit Langote <amitlangote09@gmail.com>, Imai Yoshikazu <yoshikazu_i443@live.jp>, David Rowley <david.rowley@2ndquadrant.com>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>, "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-04-01T14:29:14Z
Lists: pgsql-hackers 
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> On 2019/04/01 3:46, Tom Lane wrote:
>> One thing that I intentionally left out of the committed patch was changes
>> to stop short of scanning the whole simple_rel_array when looking only for
>> baserels.  I thought that had been done in a rather piecemeal fashion
>> and it'd be better to address it holistically, which I've now done in the
>> attached proposed patch.
>> I have not done any performance testing to see if this is actually
>> worth the trouble, though.  Anybody want to do that?

> Thanks for creating the patch.

> I spent some time looking for cases where this patch would provide
> recognizable benefit, but couldn't find one.

Yeah, I was afraid of that.  In cases where we do have a ton of otherrels,
the processing that's actually needed on them would probably swamp any
savings from this patch.

The only place where that might possibly not be true is
create_lateral_join_info, since that has nested loops that could
potentially impose an O(N^2) cost.  However, since your patch went in,
that runs before inheritance expansion anyway.

So this probably isn't worth even the minuscule cost it imposes.

			regards, tom lane

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.