Re: A problem about partitionwise join

On Mon, Mar 25, 2024 at 9:01 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

>
> create table p (k1 int, k2 int, val int) partition by range(k1, k2);
> create table p_1 partition of p for values from (1,1) to (10,100);
> create table p_2 partition of p for values from (10,100) to (20,200);
>
> set enable_partitionwise_join to on;
>
> explain (costs off)
> select * from p as foo join p as bar on foo.k1 = bar.k1 and foo.k2 =
> bar.k2 and foo.k2 = 5;
> QUERY PLAN
> -----------------------------------------
> Hash Join
> Hash Cond: (foo.k1 = bar.k1)
> -> Append
> -> Seq Scan on p_1 foo_1
> Filter: (k2 = 5)
> -> Seq Scan on p_2 foo_2
> Filter: (k2 = 5)
> -> Hash
> -> Append
> -> Seq Scan on p_1 bar_1
> Filter: (k2 = 5)
> -> Seq Scan on p_2 bar_2
> Filter: (k2 = 5)
> (13 rows)
>

Thanks for the example. You are right.

I think we need some way to avoid two different ways of looking up
partition keys - if we can't teach the EC machinery to produce clauses with
partition keys (always), we need to teach EC to contain partition keys in
case of outer joins. Tom alluded to this but I haven't seen any proposal.
The potential danger with the current patch is that it will continue to
have two loops even if we fix one of the above cases in future.

--
Best Wishes,
Ashutosh Bapat