Hi Rajkumar, On Mon, Oct 11, 2021 at 2:36 PM Rajkumar Raghuwanshi <rajkumar.raghuwan...@enterprisedb.com> wrote: > > Thanks for the patch, it applied cleanly and fixed the reported issue. I > observed another case where > In case of multi-col list partition on the same column query is not picking > partition wise join. Is this expected? > > CREATE TABLE plt1 (a int, b int, c varchar) PARTITION BY LIST(c,c); > CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN > (('0001','0001'),('0002','0002'),('0003','0003')); > CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN > (('0004','0004'),('0005','0005'),('0006','0006')); > CREATE TABLE plt1_p3 PARTITION OF plt1 DEFAULT; > INSERT INTO plt1 SELECT i, i % 47, to_char(i % 11, 'FM0000') FROM > generate_series(0, 500) i WHERE i % 11 NOT IN (0,10); > ANALYSE plt1; > CREATE TABLE plt2 (a int, b int, c varchar) PARTITION BY LIST(c,c); > CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN > (('0001','0001'),('0002','0002'),('0003','0003')); > CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN > (('0004','0004'),('0005','0005'),('0006','0006')); > CREATE TABLE plt2_p3 PARTITION OF plt2 DEFAULT; > INSERT INTO plt2 SELECT i, i % 47, to_char(i % 11, 'FM0000') FROM > generate_series(0, 500) i WHERE i % 11 NOT IN (0,10); > ANALYSE plt2; > SET enable_partitionwise_join TO true; > EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 INNER JOIN > plt2 t2 ON t1.c = t2.c; > > postgres=# EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 > INNER JOIN plt2 t2 ON t1.c = t2.c; > QUERY PLAN > -------------------------------------------- > Hash Join > Hash Cond: ((t1.c)::text = (t2.c)::text) > -> Append > -> Seq Scan on plt1_p1 t1_1 > -> Seq Scan on plt1_p2 t1_2 > -> Seq Scan on plt1_p3 t1_3 > -> Hash > -> Append > -> Seq Scan on plt2_p1 t2_1 > -> Seq Scan on plt2_p2 t2_2 > -> Seq Scan on plt2_p3 t2_3 > (11 rows)
Interesting test case. I think this might be an *existing* limitation of the code that compares join clauses against the partition key(s) to determine if partition-wise join should be considered. The clause t1.c = t2.c should have been matched with both of the partition keys (c, c), but it is not given the way have_partkey_equi_join() is currently coded. I suspect you'd get the same behavior if you'd used a RANGE partitioned table with keys (c, c). Not sure though if it'd be worthwhile to fix that coding to cater to this odd partition key setting. -- Amit Langote EDB: http://www.enterprisedb.com