Re: Partition-wise join for join between (declaratively) partitioned tables

On Tue, Apr 4, 2017 at 10:22 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> Yes, I agree. For an inner join, the partition key types need to "shrink"
> and for outer join they need to be "widened". I don't know if there is a way
> to know "wider" or "shorter" of two given types. We might have to implement
> a method to merge partition keys to produce partition key of the join, which
> may be different from either of the partition keys. So, after-all we may
> have to abandon the idea of canonical partition scheme. I haven't included
> this change in the attached set of patches.

I think this is why you need to regard the partitioning scheme as
something more like an equivalence class - possibly the partitioning
scheme should actually contain (or be?) an equivalence class. Suppose
this is the query:

SELECT * FROM i4 INNER JOIN i8 ON i4.x = i8.x;

...where i4 (x) is an int4 partitioning key and i8 (x) is an int8
partitioning key. It's meaningless to ask whether the result of the
join is partitioned by int4 or int8. It's partitioned by the
equivalence class that contains both i4.x and i8.x. If the result of
this join where joined to another table on either of those two
columns, a second partition-wise join would be theoretically possible.
If you insist on knowing the type of the partitioning scheme, rather
than just the opfamily, you've boxed yourself into a corner from which
there's no good escape.

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