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

Commitf5605c5

Browse files
committed
[PGPRO-9977] Added new expected results after vanilla commit
Tags: pg_pathmanSee b262ad440ede - Add better handling of redundant IS [NOT] NULL quals
1 parenta899f36 commitf5605c5

File tree

2 files changed

+155
-0
lines changed

2 files changed

+155
-0
lines changed

‎expected/pathman_hashjoin_6.out

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,75 @@
1+
/*
2+
* pathman_hashjoin_1.out and pathman_hashjoin_2.out seem to deal with pgpro's
3+
* different behaviour. 8edd0e794 (>= 12) Append nodes with single subplan
4+
* are eliminated, hence pathman_hashjoin_3.out
5+
*
6+
* Since 55a1954da16 and 6ef77cf46e8 (>= 13) output of EXPLAIN was changed,
7+
* now it includes aliases for inherited tables.
8+
*/
9+
\set VERBOSITY terse
10+
SET search_path = 'public';
11+
CREATE SCHEMA pathman;
12+
CREATE EXTENSION pg_pathman SCHEMA pathman;
13+
CREATE SCHEMA test;
14+
CREATE TABLE test.range_rel (
15+
idSERIAL PRIMARY KEY,
16+
dtTIMESTAMP NOT NULL,
17+
txtTEXT);
18+
CREATE INDEX ON test.range_rel (dt);
19+
INSERT INTO test.range_rel (dt, txt)
20+
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
21+
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
22+
create_range_partitions
23+
-------------------------
24+
4
25+
(1 row)
26+
27+
CREATE TABLE test.num_range_rel (
28+
idSERIAL PRIMARY KEY,
29+
txtTEXT);
30+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
31+
create_range_partitions
32+
-------------------------
33+
4
34+
(1 row)
35+
36+
INSERT INTO test.num_range_rel
37+
SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
38+
SET pg_pathman.enable_runtimeappend = OFF;
39+
SET pg_pathman.enable_runtimemergeappend = OFF;
40+
VACUUM;
41+
/*
42+
* Hash join
43+
*/
44+
SET enable_indexscan = ON;
45+
SET enable_seqscan = OFF;
46+
SET enable_nestloop = OFF;
47+
SET enable_hashjoin = ON;
48+
SET enable_mergejoin = OFF;
49+
EXPLAIN (COSTS OFF)
50+
SELECT * FROM test.range_rel j1
51+
JOIN test.range_rel j2 on j2.id = j1.id
52+
JOIN test.num_range_rel j3 on j3.id = j1.id
53+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
54+
QUERY PLAN
55+
---------------------------------------------------------------------------------
56+
Sort
57+
Sort Key: j2.dt
58+
-> Hash Join
59+
Hash Cond: (j3.id = j2.id)
60+
-> Append
61+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3_1
62+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_2
63+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_3
64+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_4
65+
-> Hash
66+
-> Index Scan using range_rel_2_dt_idx on range_rel_2 j2
67+
(11 rows)
68+
69+
DROP TABLE test.num_range_rel CASCADE;
70+
NOTICE: drop cascades to 5 other objects
71+
DROP TABLE test.range_rel CASCADE;
72+
NOTICE: drop cascades to 5 other objects
73+
DROP SCHEMA test;
74+
DROP EXTENSION pg_pathman CASCADE;
75+
DROP SCHEMA pathman;

‎expected/pathman_mergejoin_6.out

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,80 @@
1+
/*
2+
* pathman_mergejoin_1.out and pathman_mergejoin_2.out seem to deal with pgpro's
3+
* different behaviour. 8edd0e794 (>= 12) Append nodes with single subplan
4+
* are eliminated, hence pathman_mergejoin_3.out
5+
*
6+
* Since 55a1954da16 and 6ef77cf46e8 (>= 13) output of EXPLAIN was changed,
7+
* now it includes aliases for inherited tables.
8+
*
9+
* ---------------------------------------------
10+
* NOTE: This test behaves differenly on PgPro
11+
* ---------------------------------------------
12+
*/
13+
\set VERBOSITY terse
14+
SET search_path = 'public';
15+
CREATE SCHEMA pathman;
16+
CREATE EXTENSION pg_pathman SCHEMA pathman;
17+
CREATE SCHEMA test;
18+
CREATE TABLE test.range_rel (
19+
idSERIAL PRIMARY KEY,
20+
dtTIMESTAMP NOT NULL,
21+
txtTEXT);
22+
CREATE INDEX ON test.range_rel (dt);
23+
INSERT INTO test.range_rel (dt, txt)
24+
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
25+
SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL);
26+
create_range_partitions
27+
-------------------------
28+
4
29+
(1 row)
30+
31+
CREATE TABLE test.num_range_rel (
32+
idSERIAL PRIMARY KEY,
33+
txtTEXT);
34+
INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
35+
SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4);
36+
create_range_partitions
37+
-------------------------
38+
4
39+
(1 row)
40+
41+
/*
42+
* Merge join between 3 partitioned tables
43+
*
44+
* test case for the fix of sorting, merge append and index scan issues
45+
* details in commit 54dd0486fc55b2d25cf7d095f83dee6ff4adee06
46+
*/
47+
SET enable_hashjoin = OFF;
48+
SET enable_nestloop = OFF;
49+
SET enable_mergejoin = ON;
50+
SET enable_indexscan = ON;
51+
SET enable_seqscan = OFF;
52+
EXPLAIN (COSTS OFF)
53+
SELECT * FROM test.range_rel j1
54+
JOIN test.range_rel j2 on j2.id = j1.id
55+
JOIN test.num_range_rel j3 on j3.id = j1.id
56+
WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt;
57+
QUERY PLAN
58+
---------------------------------------------------------------------------------
59+
Sort
60+
Sort Key: j2.dt
61+
-> Merge Join
62+
Merge Cond: (j2.id = j3.id)
63+
-> Index Scan using range_rel_2_pkey on range_rel_2 j2
64+
-> Append
65+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3_1
66+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_2
67+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_3
68+
-> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_4
69+
(10 rows)
70+
71+
SET enable_hashjoin = ON;
72+
SET enable_nestloop = ON;
73+
SET enable_seqscan = ON;
74+
DROP TABLE test.num_range_rel CASCADE;
75+
NOTICE: drop cascades to 5 other objects
76+
DROP TABLE test.range_rel CASCADE;
77+
NOTICE: drop cascades to 5 other objects
78+
DROP SCHEMA test;
79+
DROP EXTENSION pg_pathman;
80+
DROP SCHEMA pathman;

0 commit comments

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