Re: multi-level partitions and partition-wise joins

On Wed, Dec 21, 2016 at 10:25 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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?
>

Sorry for sending mail before adding points to discuss.

Given the scenario described above, it looks like we have to retain
partition hierarchy in the form of inheritance hierarchy in order to
implement partition-wise joins for multi-leveled partition tables. Is
that the right thing to do? PFA a patch retained by Amit Langote to
translate partition hierarchy into inheritance hierarchy. Is this
something on the right direction?

Any other options I can think of like maintaining a tree of
partitioning schemes, either means that we can not plan partition-wise
joins for part of partition hierarchy e.g. matching whole partitioning
scheme tree OR it means that we have to add append plans to partition
relations corresponding to partitioned partitions, which is not
correct since leaf child relations can not have append paths. Any
suggestions?

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