Re: multi-level partitions and partition-wise joins

On Thu, Dec 22, 2016 at 10:52 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Dec 21, 2016 at 11:31 PM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> 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?
>
> I am not sure whether Amit's patch is the right way to go. I don't
> fully understand it, and I remember complaining about some aspects of
> it before, such as this unexplained and fairly random-looking
> exception:
>
> + /*
> + * Do not flatten the inheritance hierarchy if partitioned table, unless
> + * this is the result relation.
> + */
>
> However, I think the overall idea of doing flattening later in the
> process for partitioned tables is probably correct. It's not that we
> shouldn't do flattening at all -- the final Plan shouldn't involve
> nested Append nodes -- but maybe we should delay it. Perhaps the Path
> tree retains the structure and the final Plan flattens it.

While creating append paths we flatten any append paths added to the children.

> We might
> consider doing that way for both inheritance trees and partitioning,
> just so we don't have two different code paths to validate.
>

AFAIU the reason why we chose to flatten the inheritance hierarchy is
multiple inheritance. Since the same child can inherit from two
parents, in an unflattened version its paths would be included twice.
It would be clumsy to keep the inheritance unflattened but not include
a relation more than once in the final plan tree.

However, for partitioned tables, we are guaranteed that there's only a
single parent and thus every child relation will be considered only
once. We will need separate code to handle (possible) multiple
inheritance and strictly single inheritance imposed by partitioning.

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