Re: speeding up planning with partitions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Subject: Re: speeding up planning with partitions
Date: 2018-11-09 08:55:05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:


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


Patch that adjusts planner so that inheritance_planner can use partition


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.


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.


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


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.


Attachment Content-Type Size
v5-0001-Store-inheritance-root-parent-index-in-otherrel-s.patch text/plain 2.5 KB
v5-0002-Overhaul-inheritance-update-delete-planning.patch text/plain 44.9 KB
v5-0003-Lazy-creation-of-RTEs-for-inheritance-children.patch text/plain 85.1 KB
v5-0004-Move-append-expansion-code-into-its-own-file.patch text/plain 108.9 KB
v5-0005-Teach-planner-to-only-process-unpruned-partitions.patch text/plain 7.2 KB
v5-0006-Do-not-lock-all-partitions-at-the-beginning.patch text/plain 1.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Karsten Hilbert 2018-11-09 09:25:18 FIXED: backend crash on DELETE, reproducible locally
Previous Message Kyotaro HORIGUCHI 2018-11-09 08:33:15 Re: shared-memory based stats collector