On Tue, Aug 27, 2019 at 4:57 PM Richard Guo <riguo@pivotal.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.
Thank you.
Attached is a patch as an attempt to address this issue. The idea is quite straightforward. When building partition info for joinrel, we generate any possible EC-derived joinclauses of form 'outer_em = inner_em', which will be used together with the original restrictlist to check if there exists an equi-join condition for each pair of partition keys.