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

On Thu, Mar 30, 2017 at 6:32 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> So, because we want to create an Append path for each partitioned table in
> a tree separately, we'll need RelOptInfo for each one, which in turn
> requires an AppendRelInfo.

Hmm. It's no more desirable to have an Append inside of another
Append with partitionwise join than it is in general. If we've got A
partitioned into A1, A2, A3 and similarly B partitioned into B1, B2,
and B3, and then A1 and B1 are further partitioned into A1a, A1b, B1a,
B1b, then a partitionwise join between the tables ought to end up
looking like this:

Append
-> Join (A1a, B1a)
-> Join (A1b, B1b)
-> Join (A2, B2)
-> Join (A3, B3)

So here we don't actually end up with an append-path for A1-B1 here
anywhere. But you might need that in more complex cases, I guess,
because suppose you now join this to C with partitions C1, C2, C3; but
C1 is not sub-partitioned. Then you might end up with a plan like:

Append
-> Join
-> Append
-> Join (A1a, B1a)
-> Join (A1b, B1b)
-> Scan C1
-> Join ((A2, B2), C2)
-> Join ((A3, B3), C3)

So maybe you're right.

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