Horton

RE: speeding up planning with partitions

Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>

View thread View on postgr.es
From: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>
To: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>
Cc: 'Amit Langote' <amitlangote09@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, "jesper.pedersen@redhat.com" <jesper.pedersen@redhat.com>, Justin Pryzby <pryzby@telsasoft.com>, "Pg Hackers" <pgsql-hackers@postgresql.org>
Date: 2019-03-07T00:59:07Z
Lists: pgsql-hackers 
Amit-san,

On Wed, Mar 6, 2019 at 5:38 AM, Amit Langote wrote:
> On 2019/03/06 11:09, Imai, Yoshikazu wrote:
> > [0004 or 0005]
> > There are redundant process in add_appendrel_other_rels (or
> expand_xxx_rtentry()?).
> > I modified add_appendrel_other_rels like below and it actually worked.
> >
> >
> > add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index
> > rti) {
> >     ListCell       *l;
> >     RelOptInfo     *rel;
> >
> >     /*
> >      * Add inheritance children to the query if not already done. For
> child
> >      * tables that are themselves partitioned, their children will be
> added
> >      * recursively.
> >      */
> >     if (rte->rtekind == RTE_RELATION
> && !root->contains_inherit_children)
> >     {
> >         expand_inherited_rtentry(root, rte, rti);
> >         return;
> >     }
> >
> >     rel = find_base_rel(root, rti);
> >
> >     foreach(l, root->append_rel_list)
> >     {
> >         AppendRelInfo  *appinfo = lfirst(l);
> >         Index           childRTindex = appinfo->child_relid;
> >         RangeTblEntry  *childrte;
> >         RelOptInfo     *childrel;
> >
> >         if (appinfo->parent_relid != rti)
> >                 continue;
> >
> >         Assert(childRTindex < root->simple_rel_array_size);
> >         childrte = root->simple_rte_array[childRTindex];
> >         Assert(childrte != NULL);
> >         build_simple_rel(root, childRTindex, rel);
> >
> >         /* Child may itself be an inherited relation. */
> >         if (childrte->inh)
> >             add_appendrel_other_rels(root, childrte, childRTindex);
> >     }
> > }
> 
> If you don't intialize part_rels here, then it will break any code in
> the planner that expects a partitioned rel with non-zero number of
> partitions to have part_rels set to non-NULL.  At the moment, that
> includes the code that implements partitioning-specific optimizations
> such partitionwise aggregate and join, run-time pruning etc.  No existing
> regression tests cover the cases where source inherited roles
> participates in those optimizations, so you didn't find anything that
> broke.  For an example, consider the following update query:
> 
> update p set a = p1.a + 1 from p p1 where p1.a = (select 1);
> 
> Planner will create a run-time pruning aware Append node for p (aliased
> p1), for which it will need to use the part_rels array.  Note that p in
> this case is a source relation which the above code initializes.
> 
> Maybe we should add such regression tests.

Ah, now I understand that the codes below of expand_inherited_rtentry() initializes part_rels of child queries after first child target and part_rels of those are used in partitioning-specific optimizations. Thanks for the explanation.

--
Yoshikazu Imai

Commits

  1. Clean up handling of constraint_exclusion and enable_partition_pruning.

  2. Add test case exercising formerly-unreached code in inheritance_planner.

  3. Speed up planning when partitions can be pruned at plan time.

  4. Avoid crash in partitionwise join planning under GEQO.

  5. Avoid passing query tlist around separately from root->processed_tlist.

  6. Build "other rels" of appendrel baserels in a separate step.

  7. Get rid of duplicate child RTE for a partitioned table.

  8. Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.

  9. Don't copy PartitionBoundInfo in set_relation_partition_info.

  10. Move building of child base quals out into a new function

  11. Reorganize planner code moved in b60c39759908

  12. Move inheritance expansion code into its own file

  13. Fix inherited UPDATE/DELETE with UNION ALL subqueries.

  14. Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.