Re: multi-level partitions and partition-wise joins

On Wed, Dec 21, 2016 at 6:36 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> 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.

I can't quite figure out what the point of this email is. What did
you want to discuss?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company