Re: speeding up planning with partitions

Hi,

On 2019/03/01 22:01, Amit Langote wrote:
> Please find attached updated patches.  Will update source code comments,
> commit message and perform other fine-tuning over the weekend if possible.

I realized when "fine-tuning" that the patch 0001 contained too many
changes that seem logically separable.  I managed to divide it into the
following patches, which also amounts to a much shorter overall diff
against the master.  Also, the smaller individual patches made it easier
to spot a lot of useless diffs of inherit.c.

Attached patches are as follows:

1. Create the "otherrel" RelOptInfos of the appendrels as a separate step
of query_planner.  Newly added function add_other_rels_to_query() which
adds "other rel" RelOptInfos of child relations is called after
query_planner has finished distributing restrict clauses to baserels.
Child relations in this case include both those of flattened UNION ALL
subqueries and inheritance child tables.  Child RangeTblEntrys and
AppendRelInfos are added early in subquery_planner for both types of child
relations.  Of the two, we'd like to delay adding the inheritance
children, which is done in the next patch.

See patch 0001.

2. Defer inheritance expansion to add_other_rels_to_query().  Although the
purpose of doing this is to perform partition pruning before adding the
children, this patch doesn't change when the pruning occurs.  It deals
with other issues that must be taken care of due to adding children during
query_planner instead of during subquery_planner.  Especially,
inheritance_planner now has to add the child target relations on its own.
Also, delaying adding children also affects adding junk columns to the
query's targetlist based on PlanRowMarks, because preprocess_targetlist
can no longer finalize which junk columns to add for a "parent"
PlanRowMark; that must be delayed until all child PlanRowMarks are added
and their allMarkTypes propagated to the parent PlanRowMark.

See patch 0002.

3. Because inheritance_planner calls query_planner separately for each
target child relation and the patch 0002 above puts query_planner in
charge of inheritance expansion, that means child tables of source
inheritance sets will be added as many times as there are target children.
 This makes update/delete queries containing inherited source tables
somewhat slow.  This patch adjusts inheritance_planner to reuse source
inheritance children added during the planning of 1st child query for the
planning of subsequent child queries.

See patch 0003.

4. Now that all the issues arising due to late inheritance expansion have
been taken care of, this patch moves where partition pruning occurs.
Today it's in set_append_rel_size() and this patch moves it to
expand_partitioned_rtentry() where partitions are added.  Only the
partitions remaining after pruning are added, so some members of part_rels
can remain NULL.  Some of the places that access partition RelOptInfos
using that array needed to be made aware of that.

See patch 0004.

5. There are a few places which loop over *all* members of part_rels array
of a partitioned parent's RelOptInfo to do something with the partition
rels.   Some of those loops run even for point-lookup queries where only
one partition would be accessed, which is inefficient.  This patch adds a
Bitmapset member named 'live_parts' to RelOptInfo, whose value is the set
of indexes of unpruned partitions in the parent's RelOptInfo.  The
aforementioned loops are now replaced by looping over live_parts Bitmapset
instead.

See patch 0005.

6. set_relation_partition_info today copies the PartitionBoundInfo from
the relcache using partition_bounds_copy.  Doing partition_bounds_copy
gets expensive as the number of partitions increases and it really doesn't
seem necessary for the planner to create its own copy.  This patch removes
the partition_bounds_copy() and simply uses the relcache pointer.

See patch 0006.

Hope that the above division makes the changes easier to review.

Thanks,
Amit