multi-level partitions and partition-wise joins

Hi,
I am starting this as a separate thread for this since the declarative
partitioning thread has many issues reported and it's better to keep
this discussion separate from the issues reported on that thread.

While expanding inheritance, any inheritance hierarchy is flattened
out including partition hierarchy. Partition-wise joins can be
employed if the joining tables have the same partitioning scheme and
have equi-join clauses on the partition keys. If two multi-level
partitioned tables are joined, the partition-wise join can be
percolated down to the levels up to which the partition schemes match
and suitable clauses are available. E.g. if two multi-level
partitioned table have matching partitioning schemes at the top-most
level, but not below that, we may join the topmost level partitions
pair-wise, but not partitions on the lower levels. In general, we may
use partition-wise join for the matching parts of partition hierarchy
and in the parts that do not match, use join between append relations.
Not always it will be efficient to execute partition-wise joins upto
the last levels of partition hierarchy, even if partition-wise join
can be employed. It might be possible that executing partition-wise
joins for only certain parts of partition hierarchy is efficient and
join of appends is efficient in the rest of the parts.

In order to decide whether partition-wise join is efficient for a join
between given partitioned partition, we need to identify its
subpartitions. Similarly when a join between partitioned partition can
not use partition-wise join but some other partitions can, we need to
identify the subpartitions of that partition, so that they can be
appended together before joining. That information is lost while
expanding RTE. It looks like we need to retain partitioning hierarchy
in order to implement partition-wise joins between multi-level
partitioned tables.

An earlier version of Amit's partition support patches had code to
retain partitioning hierarchy but it seems it was removed per
discussion at [1]. I agree with that decision.

[1]. https://www.postgresql.org/message-id/CA%2BTgmobMy%3DrqM%3DMTN_FUEfD-PiWSCSonH%2BZ1_SjL6ZmQ2GGz1w%40mail.gmail.com

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