Re: Multi-Column List Partitioning

Hi Rajkumar,

On Mon, Oct 11, 2021 at 2:36 PM Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@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