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.