Re: speeding up planning with partitions

On Tue, 8 Jan 2019 at 19:30, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> Rebased due to changed copyright year in prepunion.c. Also realized that
> the new files added by patch 0004 still had 2018 in them.

I've made a pass over 0001. There's probably enough for you to look at
while I look at 0002 and the others.

0001

1. In your doc changes, just below a paragraph that you removed,
there's a paragraph starting "Both of these behaviors are likely to be
changed in a future release". This needs to be fixed since you've
removed the first of the two reasons.

2. This part should be left alone.

- technique similar to partition pruning. While it is primarily used
- for partitioning implemented using the legacy inheritance method, it can be
- used for other purposes, including with declarative partitioning.
+ technique similar to partition pruning. It is primarily used
+ for partitioning implemented using the legacy inheritance method.

Looking at set_inherit_target_rel_sizes(), constraint exclusion still
is applied to partitions, it's just applied after pruning, according
to:

if (did_pruning && !bms_is_member(appinfo->child_relid, live_children))
{
/* This partition was pruned; skip it. */
set_dummy_rel_pathlist(childrel);
continue;
}

if (relation_excluded_by_constraints(root, childrel, childRTE))

3. add_child_rel_equivalences(). You're replacing parent EMs with
their child equivalent, but only when the eclass has no volatile
functions. Is this really what you want? I think this would misbehave
if we ever allowed: UPDATE ... SET .. ORDER BY, of which there's a
legitimate use case of wanting to reduce the chances of deadlocks
caused by non-deterministic UPDATE order. Or if you think skipping
the volatile eclasses is fine today, then I think the comment you've
added to add_child_rel_equivalences should mention that.

4. Do you think it's okay that add_child_rel_equivalences() does not
update the ec_relids when removing the member?

UPDATE: I see you're likely leaving this alone since you're only doing
a shallow copy of the eclasses in
adjust_inherited_target_child_root(). It seems like a pretty bad idea
to do a shallow copy there.

5. What's CE?

+ /* CE failed, so finish copying/modifying join quals. */

6. Typo:

+ * ass dummy. We must do this in this phase so that the rel's

ass -> as

7. There's no accumulation going on here:

+ /*
+ * Accumulate size information from each live child.
+ */
+ Assert(childrel->rows > 0);

8. Any point in this? We're about to loop again anyway...

+ /*
+ * If child is dummy, ignore it.
+ */
+ if (IS_DUMMY_REL(childrel))
+ continue;
+ }

9. It's a bit confusing to mention SELECT in this comment. The Assert
ensures it's an UPDATE or DELETE.

+ /*
+ * For UPDATE/DELETE queries, the top parent can only ever be a table.
+ * As a contrast, it could be a UNION ALL subquery in the case of SELECT.
+ */
+ Assert(root->parse->commandType == CMD_UPDATE ||
+ root->parse->commandType == CMD_DELETE);

10. I'd say the subroot assignment can go after the IS_DUMMY_REL
check. Keeping that loop as tight as possible for pruned rels seems
like a good idea.

+ subroot = root->inh_target_child_roots[appinfo->child_relid];
+ Assert(subroot->parse->resultRelation > 0);
+ childrel = find_base_rel(root, appinfo->child_relid);
+
+ /* Ignore excluded/pruned children. */
+ if (IS_DUMMY_REL(childrel))
+ continue;

11. I don't think you should reuse the childrel variable here:

+ childrel->reloptkind = RELOPT_BASEREL;
+
+ Assert(subroot->join_rel_list == NIL);
+ Assert(subroot->join_rel_hash == NULL);
+
+ /* Perform join planning and save the resulting RelOptInfo. */
+ childrel = make_rel_from_joinlist(subroot, translated_joinlist);
+
+ /*
+ * Remember this child target rel. inheritance_planner will perform
+ * the remaining steps of planning for each child relation separately.
+ * Specifically, it will call grouping_planner on every
+ * RelOptInfo contained in the inh_target_child_rels list, each of
+ * which represents the source of tuples to be modified for a given
+ * target child rel.
+ */
+ root->inh_target_child_joinrels =
+ lappend(root->inh_target_child_joinrels, childrel);

12. The following comment makes less sense now that you've modified
the previous paragraph:

+ * Fortunately, the UPDATE/DELETE target can never be the nullable side of an
+ * outer join, so it's OK to generate the plan this way.

This text used to refer to:

but target inheritance has to be expanded at
* the top. The reason is that for UPDATE, each target relation needs a
* different targetlist matching its own column set. Fortunately,
* the UPDATE/DELETE target can never be the nullable side of an outer join,
* so it's OK to generate the plan this way.

you no longer describe plan as being expanded from the top rather than
at the bottom, which IMO is what "this way" refers to.

13. "tree is" -> "tree are" (references is plural)

+ * references in the join tree to the original target relation that's the
+ * root parent of the inheritance tree is replaced by each of its

14. Any reason to move this line from its original location?

Assert(parse->commandType != CMD_INSERT);
+ parent_rte = planner_rt_fetch(top_parentRTindex, root);

Previously it was assigned just before it was needed and there's a
fast path out after where you moved it to and where it was.

15. relation_excluded_by_constraints(), the switch
(constraint_exclusion), you could consider turning that into

if (constraint_exclusion == CONSTRAINT_EXCLUSION_OFF)
return false;
/*
* When constraint_exclusion is set to 'partition' we only handle
* OTHER_MEMBER_RELs.
*/
else if (constraint_exclusion == CONSTRAINT_EXCLUSION_PARTITION &&
rel->reloptkind != RELOPT_OTHER_MEMBER_REL)
return false;

When I wrote that code I was trying my best to make the complex rules
as simple as possible by separating them out. The rules have become
quite simple after your change, so it probably does not warrant having
the switch.

16. I think the following comment needs to explain how large this
array is and what indexes it. The current comment would have you
think there are only enough elements to store PlannerInfos for child
rels and leaves you guessing about what they're indexed by.

+ /*
+ * PlannerInfos corresponding to each inheritance child targets.
+ * Content of each PlannerInfo is same as the parent PlannerInfo, except
+ * for the parse tree which is a translated copy of the parent's parse
+ * tree.
+ */
+ struct PlannerInfo **inh_target_child_roots;

17. I'm getting an assert failure in add_paths_to_append_rel() for
Assert(parallel_workers > 0) during the partition_join tests.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services