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