On Wed, Aug 28, 2019 at 6:49 PM Etsuro Fujita <etsuro.fuj...@gmail.com> wrote:
> Hi, > > 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. > 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. Any comments are welcome! Thanks Richard
v1-0001-Fix-up-partitionwise-join.patch
Description: Binary data
