Re: speeding up planning with partitions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
Cc: 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-10-26 10:37:52
Message-ID: f06a398a-40a9-efb4-fab9-784400fecf13@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

And here is the latest set of patches. Sorry it took me a while.

* To reiterate, this set of patches is intended to delay adding partitions
to the query's range table and PlannerInfo, which leads to significantly
reduced overheads in the common case where queries accessing partitioned
tables need to scan only 1 or few partitions. As I mentioned before on
this thread [1], I wanted to change the patches such that the new approach
is adopted for all inheritance tables, not just partitioned tables,
because most of the code and data structures are shared and it no longer
made sense to me to create a diversion between the two cases by making the
partitioning a special case in a number of places in the planner code. I
think I've managed to get to that point with the latest patches. The new
code that performs inheritance expansion is common to partitioning,
inheritance, and also flattened UNION ALL to some degree, because all of
these cases use more or less the same code. For partitioning, child
relations are opened and RTEs are added for them *after* performing
pruning, whereas for inheritance that's not possible, because pruning
using constraint exclusion cannot be performed without opening the
children. This consideration is the only main thing that differs between
the handling of the two case, among other minor details that also differ.

* With the unification described above, inheritance expansion is no longer
part planner's prep phase, so I've located the new expansion code in a new
file under optimizer/utils named append.c, because it manages creation of
an appendrel's child relations. That means a lot of helper code that once
was in prepunion.c is now in the new file. Actually, I've moved *some* of
the helper routines such as adjust_appendre_* that do the mapping of
expressions between appendrel parent and children to a different file
appendinfo.c.

* I've also rewritten the patch to change how inherited update/delete
planning is done, so that it doesn't end up messing too much with
grouping_planner. Instead of removing inheritance_planner altogether as
the earlier patches did, I've rewritten it such that the CPU and memory
intensive query_planner portion is run only once at the beginning to
create scan/join paths for non-excluded/unpruned child target relations,
followed by running grouping_planner to apply top-level targetlist
suitable for each child target relation. grouping_planner is modified
slightly such that when it runs for every child target relation, it
doesn't again need to perform query planning. It instead receives the
RelOptInfos that contain the scan/join paths of child target relations
that were generated during the initial query_planner run. Beside this
main change, I've removed quite a bit of code in inheritance_planner that
relied on the existing way of redoing planning for each child target
relation. The changes are described in the commit message of patch 0002.

* A few notes on the patches:

0001 is a separate patch, because it might be useful in some other
projects like [2].

0003 is David's patch that he posted in [3].

I didn't get time today to repeat the performance tests, but I'm planning
to next week. In the meantime, please feel free to test them and provide
comments on the code changes.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/8097576f-f795-fc42-3c00-073f68bfb0b4%40lab.ntt.co.jp

[2]
https://www.postgresql.org/message-id/de957e5b-faa0-6fb9-c0ab-0b389d71cf5a%40lab.ntt.co.jp

[3] Re: Calculate total_table_pages after set_base_rel_sizes()
https://www.postgresql.org/message-id/CAKJS1f9NiQXO9KCv_cGgBShwqwT78wmArOht-5kWL%2BBt0v-AnQ%40mail.gmail.com

Attachment Content-Type Size
0001-Store-inheritance-root-parent-index-in-otherrel-s-Re.patch text/plain 2.5 KB
0002-Overhaul-inheritance-update-delete-planning.patch text/plain 44.9 KB
0003-Calculate-total_table_pages-after-set_base_rel_sizes.patch text/plain 4.8 KB
0004-Lazy-creation-of-RTEs-for-inheritance-children.patch text/plain 140.7 KB
0005-Teach-planner-to-only-process-unpruned-partitions.patch text/plain 7.1 KB
0006-Do-not-lock-all-partitions-at-the-beginning.patch text/plain 1.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Julien Rouhaud 2018-10-26 11:01:30 Re: Ordered Partitioned Table Scans
Previous Message Surafel Temesgen 2018-10-26 10:36:21 Re: COPY FROM WHEN condition