Re: A problem about partitionwise join

On Fri, Aug 30, 2019 at 2:08 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
wrote:

> On Thu, Aug 29, 2019 at 6:45 PM Richard Guo <riguo(at)pivotal(dot)io> wrote:
> > On Wed, Aug 28, 2019 at 6:49 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
> wrote:
> >> 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.
>
> > 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?
>

Added this patch: https://commitfest.postgresql.org/24/2266/

Thanks
Richard