Skip to content

Commit 4513d3a

Browse files
jeff-davisjeff-davis
committed
Add test for partitionwise join involving default partition.
Author: Rajkumar Raghuwanshi Reviewed-by: Ashutosh Bapat Discussion: https://postgr.es/m/CAKcux6ky5YeZAY74qSh-ayPZZEQchz092g71iXXbC0%2BE3xoscA%40mail.gmail.com Discussion: https://postgr.es/m/CAKcux6kOQ85Xtzxu3tM1mR7Vk%3D7Z2e4rG7dL1iMZqPgLMpxQYg%40mail.gmail.com
1 parent 3ca966c commit 4513d3a

File tree

2 files changed

+127
-0
lines changed

2 files changed

+127
-0
lines changed

‎src/test/regress/expected/partition_join.out

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1328,6 +1328,76 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, ph
13281328
273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
13291329
(6 rows)
13301330

1331+
-- test default partition behavior for range
1332+
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
1333+
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
1334+
ANALYZE prt1;
1335+
ALTER TABLE prt2 DETACH PARTITION prt2_p3;
1336+
ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
1337+
ANALYZE prt2;
1338+
EXPLAIN (COSTS OFF)
1339+
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
1340+
QUERY PLAN
1341+
--------------------------------------------------
1342+
Sort
1343+
Sort Key: t1.a
1344+
-> Append
1345+
-> Hash Join
1346+
Hash Cond: (t2.b = t1.a)
1347+
-> Seq Scan on prt2_p1 t2
1348+
-> Hash
1349+
-> Seq Scan on prt1_p1 t1
1350+
Filter: (b = 0)
1351+
-> Hash Join
1352+
Hash Cond: (t2_1.b = t1_1.a)
1353+
-> Seq Scan on prt2_p2 t2_1
1354+
-> Hash
1355+
-> Seq Scan on prt1_p2 t1_1
1356+
Filter: (b = 0)
1357+
-> Hash Join
1358+
Hash Cond: (t2_2.b = t1_2.a)
1359+
-> Seq Scan on prt2_p3 t2_2
1360+
-> Hash
1361+
-> Seq Scan on prt1_p3 t1_2
1362+
Filter: (b = 0)
1363+
(21 rows)
1364+
1365+
-- test default partition behavior for list
1366+
ALTER TABLE plt1 DETACH PARTITION plt1_p3;
1367+
ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
1368+
ANALYZE plt1;
1369+
ALTER TABLE plt2 DETACH PARTITION plt2_p3;
1370+
ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
1371+
ANALYZE plt2;
1372+
EXPLAIN (COSTS OFF)
1373+
SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.a % 25 = 0 GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
1374+
QUERY PLAN
1375+
--------------------------------------------------------
1376+
Sort
1377+
Sort Key: t1.c
1378+
-> HashAggregate
1379+
Group Key: t1.c, t2.c
1380+
-> Append
1381+
-> Hash Join
1382+
Hash Cond: (t2.c = t1.c)
1383+
-> Seq Scan on plt2_p1 t2
1384+
-> Hash
1385+
-> Seq Scan on plt1_p1 t1
1386+
Filter: ((a % 25) = 0)
1387+
-> Hash Join
1388+
Hash Cond: (t2_1.c = t1_1.c)
1389+
-> Seq Scan on plt2_p2 t2_1
1390+
-> Hash
1391+
-> Seq Scan on plt1_p2 t1_1
1392+
Filter: ((a % 25) = 0)
1393+
-> Hash Join
1394+
Hash Cond: (t2_2.c = t1_2.c)
1395+
-> Seq Scan on plt2_p3 t2_2
1396+
-> Hash
1397+
-> Seq Scan on plt1_p3 t1_2
1398+
Filter: ((a % 25) = 0)
1399+
(23 rows)
1400+
13311401
--
13321402
-- multiple levels of partitioning
13331403
--
@@ -1857,3 +1927,30 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
18571927
-> Seq Scan on prt1_n_p2 t1_1
18581928
(10 rows)
18591929

1930+
-- partitionwise join can not be applied if only one of joining table has
1931+
-- default partition
1932+
ALTER TABLE prt2 DETACH PARTITION prt2_p3;
1933+
ALTER TABLE prt2 ATTACH PARTITION prt2_p3 FOR VALUES FROM (500) TO (600);
1934+
ANALYZE prt2;
1935+
EXPLAIN (COSTS OFF)
1936+
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
1937+
QUERY PLAN
1938+
--------------------------------------------------
1939+
Sort
1940+
Sort Key: t1.a
1941+
-> Hash Join
1942+
Hash Cond: (t2.b = t1.a)
1943+
-> Append
1944+
-> Seq Scan on prt2_p1 t2
1945+
-> Seq Scan on prt2_p2 t2_1
1946+
-> Seq Scan on prt2_p3 t2_2
1947+
-> Hash
1948+
-> Append
1949+
-> Seq Scan on prt1_p1 t1
1950+
Filter: (b = 0)
1951+
-> Seq Scan on prt1_p2 t1_1
1952+
Filter: (b = 0)
1953+
-> Seq Scan on prt1_p3 t1_2
1954+
Filter: (b = 0)
1955+
(16 rows)
1956+

‎src/test/regress/sql/partition_join.sql

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -261,6 +261,27 @@ EXPLAIN (COSTS OFF)
261261
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
262262
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
263263

264+
-- test default partition behavior for range
265+
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
266+
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
267+
ANALYZE prt1;
268+
ALTER TABLE prt2 DETACH PARTITION prt2_p3;
269+
ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
270+
ANALYZE prt2;
271+
272+
EXPLAIN (COSTS OFF)
273+
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
274+
275+
-- test default partition behavior for list
276+
ALTER TABLE plt1 DETACH PARTITION plt1_p3;
277+
ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
278+
ANALYZE plt1;
279+
ALTER TABLE plt2 DETACH PARTITION plt2_p3;
280+
ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
281+
ANALYZE plt2;
282+
283+
EXPLAIN (COSTS OFF)
284+
SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.a % 25 = 0 GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
264285
--
265286
-- multiple levels of partitioning
266287
--
@@ -384,3 +405,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOI
384405
-- partitioned table
385406
EXPLAIN (COSTS OFF)
386407
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
408+
409+
-- partitionwise join can not be applied if only one of joining table has
410+
-- default partition
411+
ALTER TABLE prt2 DETACH PARTITION prt2_p3;
412+
ALTER TABLE prt2 ATTACH PARTITION prt2_p3 FOR VALUES FROM (500) TO (600);
413+
ANALYZE prt2;
414+
415+
EXPLAIN (COSTS OFF)
416+
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;

0 commit comments

Comments
 (0)