Re: A problem about partitionwise join

On Wed, Aug 28, 2019 at 6:49 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
wrote:

> 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.
>

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