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

Commit3a4f17c

Browse files
committed
Add different versions of mergejoin test case
1 parentc681907 commit3a4f17c

6 files changed

+218
-40
lines changed

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,8 @@ REGRESS = pathman_basic \
3535
pathman_runtime_nodes\
3636
pathman_utility_stmt_hooking\
3737
pathman_calamity\
38-
pathman_join_clause
38+
pathman_join_clause\
39+
pathman_mergejoin
3940

4041
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
4142

‎expected/pathman_basic.out

Lines changed: 0 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1015,38 +1015,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.
10151015
/*
10161016
* Join
10171017
*/
1018-
SET enable_hashjoin = OFF;
10191018
set enable_nestloop = OFF;
1020-
SET enable_mergejoin = ON;
1021-
EXPLAIN (COSTS OFF)
1022-
SELECT * FROM test.range_rel j1
1023-
JOIN test.range_rel j2 on j2.id = j1.id
1024-
JOIN test.num_range_rel j3 on j3.id = j1.id
1025-
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
1026-
QUERY PLAN
1027-
---------------------------------------------------------------------------------
1028-
Sort
1029-
Sort Key: j2.dt
1030-
-> Merge Join
1031-
Merge Cond: (j2.id = j3.id)
1032-
-> Merge Join
1033-
Merge Cond: (j1.id = j2.id)
1034-
-> Merge Append
1035-
Sort Key: j1.id
1036-
-> Index Scan using range_rel_1_pkey on range_rel_1 j1
1037-
-> Index Scan using range_rel_2_pkey on range_rel_2 j1_1
1038-
-> Merge Append
1039-
Sort Key: j2.id
1040-
-> Index Scan using range_rel_2_pkey on range_rel_2 j2
1041-
-> Index Scan using range_rel_3_pkey on range_rel_3 j2_1
1042-
-> Index Scan using range_rel_4_pkey on range_rel_4 j2_2
1043-
-> Append
1044-
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
1045-
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
1046-
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
1047-
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
1048-
(20 rows)
1049-
10501019
SET enable_hashjoin = ON;
10511020
SET enable_mergejoin = OFF;
10521021
EXPLAIN (COSTS OFF)

‎expected/pathman_mergejoin.out

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,88 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE SCHEMA pathman;
4+
CREATE EXTENSION pg_pathman SCHEMA pathman;
5+
CREATE SCHEMA test;
6+
CREATE TABLE test.range_rel (
7+
idSERIAL PRIMARY KEY,
8+
dtTIMESTAMP,
9+
txtTEXT);
10+
CREATE INDEX ON test.range_rel (dt);
11+
INSERT INTO test.range_rel (dt, txt)
12+
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
13+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
14+
ERROR: partitioning key "dt" must be NOT NULL
15+
ALTER TABLE test.range_rel ALTER COLUMN dt SET NOT NULL;
16+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
17+
ERROR: not enough partitions to fit all values of "dt"
18+
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
19+
NOTICE: sequence "range_rel_seq" does not exist, skipping
20+
create_range_partitions
21+
-------------------------
22+
4
23+
(1 row)
24+
25+
CREATE TABLE test.num_range_rel (
26+
idSERIAL PRIMARY KEY,
27+
txtTEXT);
28+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
29+
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
30+
create_range_partitions
31+
-------------------------
32+
4
33+
(1 row)
34+
35+
SELECT COUNT(*) FROM test.num_range_rel;
36+
count
37+
-------
38+
0
39+
(1 row)
40+
41+
SELECT COUNT(*) FROM ONLY test.num_range_rel;
42+
count
43+
-------
44+
0
45+
(1 row)
46+
47+
INSERT INTO test.num_range_rel
48+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
49+
/*
50+
* Merge join between 3 partitioned tables
51+
*
52+
* test case for the fix of sorting, merge append and index scan issues
53+
* details in commit 54dd0486fc55b2d25cf7d095f83dee6ff4adee06
54+
*/
55+
SET enable_hashjoin = OFF;
56+
SET enable_nestloop = OFF;
57+
SET enable_mergejoin = ON;
58+
EXPLAIN (COSTS OFF)
59+
SELECT * FROM test.range_rel j1
60+
JOIN test.range_rel j2 on j2.id = j1.id
61+
JOIN test.num_range_rel j3 on j3.id = j1.id
62+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
63+
QUERY PLAN
64+
-------------------------------------------------------------------------------------------
65+
Sort
66+
Sort Key: j2.dt
67+
-> Merge Join
68+
Merge Cond: (j3.id = j2.id)
69+
-> Append
70+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
71+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
72+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
73+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
74+
-> Materialize
75+
-> Merge Join
76+
Merge Cond: (j2.id = j1.id)
77+
-> Merge Append
78+
Sort Key: j2.id
79+
-> Index Scan using range_rel_2_pkey on range_rel_2 j2
80+
-> Index Scan using range_rel_3_pkey on range_rel_3 j2_1
81+
-> Index Scan using range_rel_4_pkey on range_rel_4 j2_2
82+
-> Materialize
83+
-> Merge Append
84+
Sort Key: j1.id
85+
-> Index Scan using range_rel_1_pkey on range_rel_1 j1
86+
-> Index Scan using range_rel_2_pkey on range_rel_2 j1_1
87+
(22 rows)
88+

‎expected/pathman_mergejoin_0.out

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE SCHEMA pathman;
4+
CREATE EXTENSION pg_pathman SCHEMA pathman;
5+
CREATE SCHEMA test;
6+
CREATE TABLE test.range_rel (
7+
idSERIAL PRIMARY KEY,
8+
dtTIMESTAMP,
9+
txtTEXT);
10+
CREATE INDEX ON test.range_rel (dt);
11+
INSERT INTO test.range_rel (dt, txt)
12+
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
13+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
14+
ERROR: partitioning key "dt" must be NOT NULL
15+
ALTER TABLE test.range_rel ALTER COLUMN dt SET NOT NULL;
16+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
17+
ERROR: not enough partitions to fit all values of "dt"
18+
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
19+
NOTICE: sequence "range_rel_seq" does not exist, skipping
20+
create_range_partitions
21+
-------------------------
22+
4
23+
(1 row)
24+
25+
CREATE TABLE test.num_range_rel (
26+
idSERIAL PRIMARY KEY,
27+
txtTEXT);
28+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
29+
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
30+
create_range_partitions
31+
-------------------------
32+
4
33+
(1 row)
34+
35+
SELECT COUNT(*) FROM test.num_range_rel;
36+
count
37+
-------
38+
0
39+
(1 row)
40+
41+
SELECT COUNT(*) FROM ONLY test.num_range_rel;
42+
count
43+
-------
44+
0
45+
(1 row)
46+
47+
INSERT INTO test.num_range_rel
48+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
49+
/*
50+
* Merge join between 3 partitioned tables
51+
*
52+
* test case for the fix of sorting, merge append and index scan issues
53+
* details in commit 54dd0486fc55b2d25cf7d095f83dee6ff4adee06
54+
*/
55+
SET enable_hashjoin = OFF;
56+
SET enable_nestloop = OFF;
57+
SET enable_mergejoin = ON;
58+
EXPLAIN (COSTS OFF)
59+
SELECT * FROM test.range_rel j1
60+
JOIN test.range_rel j2 on j2.id = j1.id
61+
JOIN test.num_range_rel j3 on j3.id = j1.id
62+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
63+
QUERY PLAN
64+
---------------------------------------------------------------------------------
65+
Sort
66+
Sort Key: j2.dt
67+
-> Merge Join
68+
Merge Cond: (j2.id = j3.id)
69+
-> Merge Join
70+
Merge Cond: (j1.id = j2.id)
71+
-> Merge Append
72+
Sort Key: j1.id
73+
-> Index Scan using range_rel_1_pkey on range_rel_1 j1
74+
-> Index Scan using range_rel_2_pkey on range_rel_2 j1_1
75+
-> Merge Append
76+
Sort Key: j2.id
77+
-> Index Scan using range_rel_2_pkey on range_rel_2 j2
78+
-> Index Scan using range_rel_3_pkey on range_rel_3 j2_1
79+
-> Index Scan using range_rel_4_pkey on range_rel_4 j2_2
80+
-> Append
81+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
82+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
83+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
84+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
85+
(20 rows)
86+

‎sql/pathman_basic.sql

Lines changed: 0 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -247,15 +247,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.
247247
/*
248248
* Join
249249
*/
250-
SET enable_hashjoin= OFF;
251250
set enable_nestloop= OFF;
252-
SET enable_mergejoin=ON;
253-
254-
EXPLAIN (COSTS OFF)
255-
SELECT*FROMtest.range_rel j1
256-
JOINtest.range_rel j2onj2.id=j1.id
257-
JOINtest.num_range_rel j3onj3.id=j1.id
258-
WHEREj1.dt<'2015-03-01'ANDj2.dt>='2015-02-01'ORDER BYj2.dt;
259251
SET enable_hashjoin=ON;
260252
SET enable_mergejoin= OFF;
261253
EXPLAIN (COSTS OFF)

‎sql/pathman_mergejoin.sql

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
\set VERBOSITY terse
2+
3+
SET search_path='public';
4+
CREATESCHEMApathman;
5+
CREATE EXTENSION pg_pathman SCHEMA pathman;
6+
CREATESCHEMAtest;
7+
8+
CREATETABLEtest.range_rel (
9+
idSERIALPRIMARY KEY,
10+
dtTIMESTAMP,
11+
txtTEXT);
12+
CREATEINDEXONtest.range_rel (dt);
13+
INSERT INTOtest.range_rel (dt, txt)
14+
SELECT g, md5(g::TEXT)FROM generate_series('2015-01-01','2015-04-30','1 day'::interval)as g;
15+
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,2);
16+
ALTERTABLEtest.range_rel ALTER COLUMN dtSETNOT NULL;
17+
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,2);
18+
SELECTpathman.create_range_partitions('test.range_rel','DT','2015-01-01'::DATE,'1 month'::INTERVAL);
19+
20+
CREATETABLEtest.num_range_rel (
21+
idSERIALPRIMARY KEY,
22+
txtTEXT);
23+
SELECTpathman.create_range_partitions('test.num_range_rel','id',0,1000,4);
24+
SELECTCOUNT(*)FROMtest.num_range_rel;
25+
SELECTCOUNT(*)FROM ONLYtest.num_range_rel;
26+
INSERT INTOtest.num_range_rel
27+
SELECT g, md5(g::TEXT)FROM generate_series(1,3000)as g;
28+
29+
/*
30+
* Merge join between 3 partitioned tables
31+
*
32+
* test case for the fix of sorting, merge append and index scan issues
33+
* details in commit 54dd0486fc55b2d25cf7d095f83dee6ff4adee06
34+
*/
35+
SET enable_hashjoin= OFF;
36+
SET enable_nestloop= OFF;
37+
SET enable_mergejoin=ON;
38+
EXPLAIN (COSTS OFF)
39+
SELECT*FROMtest.range_rel j1
40+
JOINtest.range_rel j2onj2.id=j1.id
41+
JOINtest.num_range_rel j3onj3.id=j1.id
42+
WHEREj1.dt<'2015-03-01'ANDj2.dt>='2015-02-01'ORDER BYj2.dt;

0 commit comments

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