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

Commitdd072e5

Browse files
committed
Add forgotten files
1 parent085f236 commitdd072e5

File tree

2 files changed

+131
-0
lines changed

2 files changed

+131
-0
lines changed

‎expected/pathman_hashjoin_2.out

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
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 NOT NULL,
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);
14+
create_range_partitions
15+
-------------------------
16+
4
17+
(1 row)
18+
19+
CREATE TABLE test.num_range_rel (
20+
idSERIAL PRIMARY KEY,
21+
txtTEXT);
22+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
23+
create_range_partitions
24+
-------------------------
25+
4
26+
(1 row)
27+
28+
INSERT INTO test.num_range_rel
29+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
30+
SET pg_pathman.enable_runtimeappend = OFF;
31+
SET pg_pathman.enable_runtimemergeappend = OFF;
32+
VACUUM;
33+
/*
34+
* Hash join
35+
*/
36+
SET enable_indexscan = ON;
37+
SET enable_seqscan = OFF;
38+
SET enable_nestloop = OFF;
39+
SET enable_hashjoin = ON;
40+
SET enable_mergejoin = OFF;
41+
EXPLAIN (COSTS OFF)
42+
SELECT * FROM test.range_rel j1
43+
JOIN test.range_rel j2 on j2.id = j1.id
44+
JOIN test.num_range_rel j3 on j3.id = j1.id
45+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
46+
QUERY PLAN
47+
---------------------------------------------------------------------------------
48+
Sort
49+
Sort Key: j2.dt
50+
-> Hash Join
51+
Hash Cond: (j3.id = j2.id)
52+
-> Append
53+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
54+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
55+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
56+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
57+
-> Hash
58+
-> Append
59+
-> Index Scan using range_rel_2_dt_idx on range_rel_2 j2
60+
Filter: (id IS NOT NULL)
61+
(13 rows)
62+
63+
DROP SCHEMA test CASCADE;
64+
NOTICE: drop cascades to 12 other objects
65+
DROP EXTENSION pg_pathman CASCADE;
66+
DROP SCHEMA pathman CASCADE;

‎expected/pathman_mergejoin_2.out

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
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 NOT NULL,
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);
14+
create_range_partitions
15+
-------------------------
16+
4
17+
(1 row)
18+
19+
CREATE TABLE test.num_range_rel (
20+
idSERIAL PRIMARY KEY,
21+
txtTEXT);
22+
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
23+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
24+
create_range_partitions
25+
-------------------------
26+
4
27+
(1 row)
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 * FROM test.range_rel j1
40+
JOIN test.range_rel j2 on j2.id = j1.id
41+
JOIN test.num_range_rel j3 on j3.id = j1.id
42+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
43+
QUERY PLAN
44+
---------------------------------------------------------------------------------
45+
Sort
46+
Sort Key: j2.dt
47+
-> Merge Join
48+
Merge Cond: (j2.id = j3.id)
49+
-> Merge Append
50+
Sort Key: j2.id
51+
-> Index Scan using range_rel_2_pkey on range_rel_2 j2
52+
Index Cond: (id IS NOT NULL)
53+
-> Append
54+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3
55+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1
56+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2
57+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3
58+
(13 rows)
59+
60+
SET enable_hashjoin = ON;
61+
SET enable_nestloop = ON;
62+
DROP SCHEMA test CASCADE;
63+
NOTICE: drop cascades to 12 other objects
64+
DROP EXTENSION pg_pathman;
65+
DROP SCHEMA pathman CASCADE;

0 commit comments

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