On Thu, Aug 29, 2019 at 6:45 PM Richard Guo <ri...@pivotal.io> wrote: > On Wed, Aug 28, 2019 at 6:49 PM Etsuro Fujita <etsuro.fuj...@gmail.com> wrote: >> On Tue, Aug 27, 2019 at 4:57 PM Richard Guo <ri...@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.
> 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. Thank you for the patch! Will review. Could you add the patch to the upcoming CF so that it doesn’t get lost? Best regards, Etsuro Fujita