On Mon, Mar 25, 2024 at 9:01 AM Richard Guo <guofengli...@gmail.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
