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: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>, Pg Hackers <pgsql-hackers@postgresql.org>, David Rowley <david.rowley@2ndquadrant.com>
Date: 2018-11-09T08:55:05Z
Lists: pgsql-hackers

Attachments

On 2018/11/07 12:44, Amit Langote wrote:
> Thanks for looking.
> 
> On 2018/11/07 12:32, Alvaro Herrera wrote:
>> ISTM patch 0004 is impossible to review just because of size -- I
>> suppose the bulk of it is just code that moves from one file to another,
>> but that there are also code changes in it.  Maybe it would be better to
>> split it so that one patch moves the code to the new files without
>> changing it, then another patch does the actual functional changes?  If
>> git shows the first half as a rename, it's easier to be confident about
>> it.
> 
> Okay, will post a new version structured that way shortly.

And here are patches structured that way.  I've addressed some of the
comments posted by Imai-san upthread.  Also, since David's patch to
postpone PlannerInfo.total_pages calculation went into the tree earlier
this week, it's no longer included in this set.

With this breakdown, patches are as follows:

v5-0001-Store-inheritance-root-parent-index-in-otherrel-s.patch

  Adds a inh_root_parent field that's set in inheritance child otherrel
  RelOptInfos to store the RT index of their root parent

v5-0002-Overhaul-inheritance-update-delete-planning.patch

  Patch that adjusts planner so that inheritance_planner can use partition
  pruning.

v5-0003-Lazy-creation-of-RTEs-for-inheritance-children.patch

  Patch that adjusts planner such that inheritance expansion step is
  postponed from early subquery_planner to the beginning of
  set_append_rel_size, so that child tables are added to the Query and
  PlannerInfo after partition pruning.  While the move only benefits
  partitioning, because non-partition children cannot be pruned until
  after they're fully opened, the new code handles both partitioned tables
  and regular inheritance parents.

v5-0004-Move-append-expansion-code-into-its-own-file.patch

  With patch 0003, inheritance expansion is no longer a part of the prep
  phase of planning, so it seems odd that inheritance expansion code is in
  optimizer/prep/prepunion.c.  This patch moves the code to a new file
  optimizer/utils/append.c.  Also, some of the appendrel translation
  subroutines are moved over to optimizer/utils/appendinfo.c.

  No functional changes with this patch.

v5-0005-Teach-planner-to-only-process-unpruned-partitions.patch

  Patch that adds a live_parts field to RelOptInfo which is set in
  partitioned rels to contain partition indexes of only non-dummy children
  replace the loops of the following form:

    for (i = 0; i < rel->nparts; i++)
    {
        RelOptInfo *partrel = rel->part_rels[i];

        ...some processing
    }

  at various points within the planner with:

    i = -1
    while ((i = bms_get_next(rel->live_parts)) >= 0)
    {
        RelOptInfo *partrel = rel->part_rels[i];

        ...some processing
    }

v5-0006-Do-not-lock-all-partitions-at-the-beginning.patch

  A simple patch that removes the find_all_inheritors called for
  partitioned root parent only to lock *all* partitions, in favor of
  locking only the unpruned partitions.

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.