Re: speeding up planning with partitions

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