Unmatched test and comment in partition_join.sql regression test
I noticed this in the regression test while polishing the PWJ-enhancement patch:
-- partitionwise join can not be applied for a join between list and range
-- partitioned tables
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
The test doesn't match the comment which precedes it, because both
tables are range-partitioned as shown below.
\d+ prt1_n
Partitioned table "public.prt1_n"
Column | Type | Collation | Nullable | Default | Storage
| Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain
| |
b | integer | | | | plain
| |
c | character varying | | | |
extended | |
Partition key: RANGE (c)
Partitions: prt1_n_p1 FOR VALUES FROM ('0000') TO ('0250'),
prt1_n_p2 FOR VALUES FROM ('0250') TO ('0500')
\d+ prt1
Partitioned table "public.prt1"
Column | Type | Collation | Nullable | Default | Storage
| Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain
| |
b | integer | | | | plain
| |
c | character varying | | | |
extended | |
Partition key: RANGE (a)
Partitions: prt1_p1 FOR VALUES FROM (0) TO (250),
prt1_p2 FOR VALUES FROM (250) TO (500),
prt1_p3 DEFAULT
I think the test should be moved to a more appropriate place, and the
comment should be moved to a test that really performs a join between
list and range partitioned tables. Attached is a patch for that. The
patch fixes another misplaced comment as well.
Best regards,
Etsuro Fujita
Attachments:
clean-up-partition-join-test.patchapplication/octet-stream; name=clean-up-partition-join-test.patchDownload
*** a/src/test/regress/expected/partition_join.out
--- b/src/test/regress/expected/partition_join.out
***************
*** 1996,2003 **** SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.c = t2.c;
-> Seq Scan on prt2_m_p3 t2_3
(11 rows)
! -- partitionwise join can not be applied between tables with different
! -- partition lists
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c);
QUERY PLAN
--- 1996,2021 ----
-> Seq Scan on prt2_m_p3 t2_3
(11 rows)
! -- equi-join between key column and non-key column does not qualify for
! -- partitionwise join
! EXPLAIN (COSTS OFF)
! SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
! QUERY PLAN
! ----------------------------------------------
! Hash Full Join
! Hash Cond: ((t2.c)::text = (t1.c)::text)
! -> Append
! -> Seq Scan on prt1_p1 t2_1
! -> Seq Scan on prt1_p2 t2_2
! -> Seq Scan on prt1_p3 t2_3
! -> Hash
! -> Append
! -> Seq Scan on prt1_n_p1 t1_1
! -> Seq Scan on prt1_n_p2 t1_2
! (10 rows)
!
! -- partitionwise join can not be applied for a join between list and range
! -- partitioned tables
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c);
QUERY PLAN
***************
*** 2013,2018 **** SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c
--- 2031,2038 ----
-> Seq Scan on prt1_n_p2 t1_2
(9 rows)
+ -- partitionwise join can not be applied between tables with different
+ -- partition lists
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOIN plt1 t3 ON (t1.c = t3.c);
QUERY PLAN
***************
*** 2035,2058 **** SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOI
-> Seq Scan on prt1_n_p2 t1_2
(16 rows)
- -- partitionwise join can not be applied for a join between list and range
- -- partitioned tables
- EXPLAIN (COSTS OFF)
- SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
- QUERY PLAN
- ----------------------------------------------
- Hash Full Join
- Hash Cond: ((t2.c)::text = (t1.c)::text)
- -> Append
- -> Seq Scan on prt1_p1 t2_1
- -> Seq Scan on prt1_p2 t2_2
- -> Seq Scan on prt1_p3 t2_3
- -> Hash
- -> Append
- -> Seq Scan on prt1_n_p1 t1_1
- -> Seq Scan on prt1_n_p2 t1_2
- (10 rows)
-
-- partitionwise join can not be applied if only one of joining tables has
-- default partition
ALTER TABLE prt2 DETACH PARTITION prt2_p3;
--- 2055,2060 ----
*** a/src/test/regress/sql/partition_join.sql
--- b/src/test/regress/sql/partition_join.sql
***************
*** 430,446 **** SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.a = t2.b;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.c = t2.c;
! -- partitionwise join can not be applied between tables with different
! -- partition lists
! EXPLAIN (COSTS OFF)
! SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c);
EXPLAIN (COSTS OFF)
! SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOIN plt1 t3 ON (t1.c = t3.c);
-- partitionwise join can not be applied for a join between list and range
-- partitioned tables
EXPLAIN (COSTS OFF)
! SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
-- partitionwise join can not be applied if only one of joining tables has
-- default partition
--- 430,449 ----
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.c = t2.c;
! -- equi-join between key column and non-key column does not qualify for
! -- partitionwise join
EXPLAIN (COSTS OFF)
! SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
-- partitionwise join can not be applied for a join between list and range
-- partitioned tables
EXPLAIN (COSTS OFF)
! SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c);
!
! -- partitionwise join can not be applied between tables with different
! -- partition lists
! EXPLAIN (COSTS OFF)
! SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOIN plt1 t3 ON (t1.c = t3.c);
-- partitionwise join can not be applied if only one of joining tables has
-- default partition
On Fri, Dec 13, 2019 at 7:17 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
I noticed this in the regression test while polishing the PWJ-enhancement patch:
-- partitionwise join can not be applied for a join between list and range
-- partitioned tables
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);The test doesn't match the comment which precedes it, because both
tables are range-partitioned as shown below.
I think the test should be moved to a more appropriate place,
On second thought I changed my mind; we would not need to move that
test, so I refrained from doing so.
Attached is a patch for that. The
patch fixes another misplaced comment as well.
I pushed an updated version of the patch.
Best regards,
Etsuro Fujita