Re: A problem about partitionwise join - Mailing list pgsql-hackers

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

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