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: Tom Lane <tgl@sss.pgh.pa.us>, Amit Langote <amitlangote09@gmail.com>
Cc: 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-01T08:37:23Z
Lists: pgsql-hackers 
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.
> 
> This probably makes little if any difference in the test cases we've
> mostly focused on in this thread, since there wouldn't be very many
> otherrels anyway now that we don't create them for pruned partitions.
> However, in a case where there's a lot of partitions that we can't prune,
> this could be useful.
> 
> 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.  As one would suspect, it's
hard to notice it if only looking at the overall latency of the query,
because time spent doing other things with such plans tends to be pretty
huge anyway (both in the planner itself and other parts of the backend).
I even devised a query on a partitioned table such that planner has to
process all partitions, but ExecInitAppend can prune all but one, thus
reducing the time spent in the executor, but still wasn't able to see an
improvement in the overall latency of the query due to planner not doing
looping over the long simple_rel_array.

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.