Re: A problem about partitionwise join

Hi,

On Tue, Aug 27, 2019 at 4:57 PM Richard Guo <riguo(at)pivotal(dot)io> wrote:
> Check the query below as a more illustrative example:
>
> create table p (k int, val int) partition by range(k);
> create table p_1 partition of p for values from (1) to (10);
> create table p_2 partition of p for values from (10) to (100);
>
> If we use quals 'foo.k = bar.k and foo.k = bar.val', we can generate
> partitionwise join:
>
> # explain (costs off)
> select * from p as foo join p as bar on foo.k = bar.k and foo.k = bar.val;
> QUERY PLAN
> -----------------------------------------
> Append
> -> Hash Join
> Hash Cond: (foo.k = bar.k)
> -> Seq Scan on p_1 foo
> -> Hash
> -> Seq Scan on p_1 bar
> Filter: (k = val)
> -> Hash Join
> Hash Cond: (foo_1.k = bar_1.k)
> -> Seq Scan on p_2 foo_1
> -> Hash
> -> Seq Scan on p_2 bar_1
> Filter: (k = val)
> (13 rows)
>
> But if we exchange the order of the two quals to 'foo.k = bar.val and
> foo.k = bar.k', then partitionwise join cannot be generated any more,
> because we only have joinclause 'foo.k = bar.val' as it first reached
> score of 3. We have missed the joinclause on the partition key although
> it does exist.
>
> # explain (costs off)
> select * from p as foo join p as bar on foo.k = bar.val and foo.k = bar.k;
> QUERY PLAN
> -----------------------------------------
> Hash Join
> Hash Cond: (foo.k = bar.val)
> -> Append
> -> Seq Scan on p_1 foo
> -> Seq Scan on p_2 foo_1
> -> Hash
> -> Append
> -> Seq Scan on p_1 bar
> Filter: (val = k)
> -> Seq Scan on p_2 bar_1
> Filter: (val = k)
> (11 rows)

I think it would be nice if we can address this issue.

Best regards,
Etsuro Fujita