Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4513d3a

Browse files
committed
Add test for partitionwise join involving default partition.
Author: Rajkumar RaghuwanshiReviewed-by: Ashutosh BapatDiscussion:https://postgr.es/m/CAKcux6ky5YeZAY74qSh-ayPZZEQchz092g71iXXbC0%2BE3xoscA%40mail.gmail.comDiscussion:https://postgr.es/m/CAKcux6kOQ85Xtzxu3tM1mR7Vk%3D7Z2e4rG7dL1iMZqPgLMpxQYg%40mail.gmail.com
1 parent3ca966c commit4513d3a

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
SELECTavg(t1.a),avg(t2.b),avg(t3.a+t3.b),t1.c,t2.c,t3.cFROM pht1 t1, pht2 t2, pht1_e t3WHEREt1.b=t2.bANDt1.c=t2.cAND ltrim(t3.c,'A')=t1.cGROUP BYt1.c,t2.c,t3.cORDER BYt1.c,t2.c,t3.c;
262262
SELECTavg(t1.a),avg(t2.b),avg(t3.a+t3.b),t1.c,t2.c,t3.cFROM pht1 t1, pht2 t2, pht1_e t3WHEREt1.b=t2.bANDt1.c=t2.cAND ltrim(t3.c,'A')=t1.cGROUP BYt1.c,t2.c,t3.cORDER BYt1.c,t2.c,t3.c;
263263

264+
-- test default partition behavior for range
265+
ALTERTABLE prt1 DETACH PARTITION prt1_p3;
266+
ALTERTABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
267+
ANALYZE prt1;
268+
ALTERTABLE prt2 DETACH PARTITION prt2_p3;
269+
ALTERTABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
270+
ANALYZE prt2;
271+
272+
EXPLAIN (COSTS OFF)
273+
SELECTt1.a,t1.c,t2.b,t2.cFROM prt1 t1, prt2 t2WHEREt1.a=t2.bANDt1.b=0ORDER BYt1.a,t2.b;
274+
275+
-- test default partition behavior for list
276+
ALTERTABLE plt1 DETACH PARTITION plt1_p3;
277+
ALTERTABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
278+
ANALYZE plt1;
279+
ALTERTABLE plt2 DETACH PARTITION plt2_p3;
280+
ALTERTABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
281+
ANALYZE plt2;
282+
283+
EXPLAIN (COSTS OFF)
284+
SELECTavg(t1.a),avg(t2.b),t1.c,t2.cFROM plt1 t1RIGHT JOIN plt2 t2ONt1.c=t2.cWHEREt1.a %25=0GROUP BYt1.c,t2.cORDER BYt1.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
SELECTt1.a,t1.c,t2.b,t2.cFROM prt1_n t1 FULLJOIN prt1 t2ON (t1.c=t2.c);
408+
409+
-- partitionwise join can not be applied if only one of joining table has
410+
-- default partition
411+
ALTERTABLE prt2 DETACH PARTITION prt2_p3;
412+
ALTERTABLE prt2 ATTACH PARTITION prt2_p3 FORVALUESFROM (500) TO (600);
413+
ANALYZE prt2;
414+
415+
EXPLAIN (COSTS OFF)
416+
SELECTt1.a,t1.c,t2.b,t2.cFROM prt1 t1, prt2 t2WHEREt1.a=t2.bANDt1.b=0ORDER BYt1.a,t2.b;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp