Re: Multi-Column List Partitioning

Hi Rajkumar,

On Mon, Oct 11, 2021 at 2:36 PM Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)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