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

On Wed, Oct 11, 2017 at 10:43 PM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> You are suggesting that a dummy partitioned table be treated as an
> un-partitioned table and apply above suggested optimization. A join
> between a partitioned and unpartitioned table is partitioned by the
> keys of only partitioned table. An unpartitioned table doesn't have
> any keys, so this is fine. But a dummy partitioned table does have
> keys. Recording them as keys of the join relation helps when it joins
> to other relations. Furthermore a join between partitioned and
> unpartitioned table doesn't require any equi-join condition on
> partition keys of partitioned table but a join between partitioned
> tables is considered to be partitioned by keys on both sides only when
> there is an equi-join. So, when implementing a partitioned join
> between a partitioned and an unpartitioned table, we will have to make
> a special case to record partition keys when the unpartitioned side is
> actually a dummy partitioned table. That might be awkward.

It seems to me that what we really need here is to move all of this
stuff into a separate struct:

        /* used for partitioned relations */
        PartitionScheme part_scheme;    /* Partitioning scheme. */
        int                     nparts;                 /* number of
partitions */
        struct PartitionBoundInfoData *boundinfo;       /* Partition bounds */
        struct RelOptInfo **part_rels;  /* Array of RelOptInfos of partitions,

  * stored in the same order of bounds */
        List      **partexprs;          /* Non-nullable partition key
expressions. */
        List      **nullable_partexprs; /* Nullable partition key
expressions. */

...and then have a RelOptInfo carry a pointer to a list of those
structures.  That lets us consider multiple possible partition schemes
for the same relation.  For instance, suppose that a user joins four
relations, P1, P2, Q1, and Q2.  P1 and P2 are compatibly partitioned.
Q1 and Q2 are compatibly partitioned (but not compatible with P1 and
P2).

Furthermore, let's suppose that the optimal join order begins with a
join between P1 and Q1.  When we construct the paths for that joinrel,
we can either join all of P1 to all of Q1 (giving up on partition-wise
join), or we can join each partition of P1 to all of Q1 (producing a
result partitioned compatibly with P1 and allowing for a future
partition-wise join to P2), or we can join each partition of Q1 to all
of P1 (producing a result partitioned compatibly with Q1 and allowing
for a future partition-wise join to Q2).  Any of those could win
depending on the details.  With the data structure as it is today,
we'd have to choose whether to mark the joinrel as partitioned like P1
or like Q1, but that's not really what we need here.

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