Re: expanding inheritance in partition bound order

On Sat, Aug 19, 2017 at 1:21 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Aug 18, 2017 at 1:17 AM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> 0004 patch in partition-wise join patchset has code to expand
>> partition hierarchy. That patch is expanding inheritance hierarchy in
>> depth first manner. Robert commented that instead of depth first
>> manner, it will be better if we expand it in partitioned tables first
>> manner. With the latest changes in your patch-set I don't see the
>> reason for expanding in partitioned tables first order. Can you please
>> elaborate if we still need to expand in partitioned table first
>> manner? May be we should just address the expansion issue in 0004
>> instead of dividing it in two patches.
>
> Let me see if I can clarify. I think there are three requirements here:
>
> A. Amit wants to be able to prune leaf partitions before opening and
> locking those relations, so that pruning can be done earlier and,
> therefore, more cheaply.

We could actually prune partitioned tables thus pruning whole
partitioned tree. Do we want to then lock those partitioned tables but
not the leaves in that tree?

If there's already some discussion answering this question, please
point me to the same. Sorry for not paying attention to it.

>
> B. Partition-wise join wants to expand the inheritance hierarchy a
> level at a time instead of all at once, ending up with rte->inh = true
> entries for intermediate partitioned tables.

And create AppendRelInfos which pair children with their partitioned
parent rather than the root.

>
> C. Partition-wise join (and lots of other things; see numerous
> mentions of EIBO in
> http://rhaas.blogspot.com/2017/08/plans-for-partitioning-in-v11.html)
> want to expand in bound order.
>
> Obviously, bound-order and partitioned-tables-first are incompatible
> orderings, but there's no actual conflict because the first one has to
> do with the order of *expansion* and the second one has to do with the
> order of *locking*.

right. Thanks for making it clear.

> So in the end game I think
> expand_inherited_rtentry looks approximately like this:
>
> 1. Calling find_all_inheritors with a new only-lock-the-partitions
> flag. This should result in locking all partitioned tables in the
> inheritance hierarchy in breadth-first, low-OID-first order. (When
> the only-lock-the-partitions isn't specified, all partitioned tables
> should still be locked before any unpartitioned tables, so that the
> locking order in that case is consistent with what we do here.)
>

I am confused. When "only-lock-the-partitions" is true, do we expect
intermediate partitioned tables to be locked? Why then "only" in the
flag?

> 2. Iterate over the partitioned tables identified in step 1 in the
> order in which they were returned. For each one:
> - Decide which children can be pruned.
> - Lock the unpruned, non-partitioned children in low-OID-first order.
>
> 3. Make another pass over the inheritance hierarchy, starting at the
> root. Traverse the whole hierarchy in breadth-first in *bound* order.
> Add RTEs and AppendRelInfos as we go -- these will have rte->inh =
> true for partitioned tables and rte->inh = false for leaf partitions.

These two seem to be based on the assumption that we have to lock all
the partitioned tables even if they can be pruned.

For regular inheritance there is only a single parent, so traversing
the list returned by find_all_inheritors suffices. For partitioned
hierarchy, we need to know the parent of every child, which is not
part of the find_all_inheritors() output list. Even if it returns only
the partitioned children, they themselves may have a parent different
from the root partition. So, we have to discard the output of
find_all_inheritors() for partitioned hierarchy and traverse the
children as per their orders in oids array in PartitionDesc. May be
it's better to separate the guts of expand_inherited_rtentry(), which
create AppendRelInfos, RTEs and rowmarks for the children into a
separate routine. Use that routine in two different functions
expand_inherited_rtentry() and expand_partitioned_rtentry() for
regular inheritance and partitioned inheritance resp. The functions
will use two different traversal methods appropriate for traversing
the children in either case.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company