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

Commit777917d

Browse files
committed
Add first regression test on join clause
1 parentf6dd5a0 commit777917d

File tree

3 files changed

+148
-1
lines changed

3 files changed

+148
-1
lines changed

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,8 @@ REGRESS = pathman_basic \
3434
pathman_rowmarks\
3535
pathman_runtime_nodes\
3636
pathman_utility_stmt_hooking\
37-
pathman_calamity
37+
pathman_calamity\
38+
pathman_join_clause
3839

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

‎expected/pathman_join_clause.out

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
\set VERBOSITY terse
2+
CREATE SCHEMA pathman;
3+
CREATE EXTENSION pg_pathman SCHEMA pathman;
4+
CREATE SCHEMA test;
5+
/*
6+
* Test push down a join clause into child nodes of append
7+
*/
8+
/* create test tables */
9+
CREATE TABLE fk (
10+
id1 INT NOT NULL,
11+
id2 INT NOT NULL,
12+
start_key INT,
13+
end_key INT,
14+
PRIMARY KEY (id1, id2));
15+
CREATE TABLE mytbl (
16+
id1 INT NOT NULL,
17+
id2 INT NOT NULL,
18+
key INT NOT NULL,
19+
CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES fk(id1, id2),
20+
PRIMARY KEY (id1, key));
21+
SELECT pathman.create_hash_partitions('mytbl', 'id1', 8);
22+
create_hash_partitions
23+
------------------------
24+
8
25+
(1 row)
26+
27+
/* ...fill out with test data */
28+
INSERT INTO fk VALUES (1, 1);
29+
INSERT INTO mytbl VALUES (1, 1, 5), (1,1,6);
30+
/* gather statistics on test tables to have deterministic plans */
31+
ANALYZE fk;
32+
ANALYZE mytbl;
33+
/* run test queries */
34+
EXPLAIN (COSTS OFF) /* test plan */
35+
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
36+
FROM mytbl m JOIN fk USING(id1, id2)
37+
WHERE NOT key <@ int4range(6, end_key);
38+
QUERY PLAN
39+
------------------------------------------------------------------------------------
40+
Nested Loop
41+
-> Seq Scan on fk
42+
-> Custom Scan (RuntimeAppend)
43+
-> Bitmap Heap Scan on mytbl_0 m
44+
Recheck Cond: (id1 = fk.id1)
45+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
46+
-> Bitmap Index Scan on mytbl_0_pkey
47+
Index Cond: (id1 = fk.id1)
48+
-> Bitmap Heap Scan on mytbl_1 m
49+
Recheck Cond: (id1 = fk.id1)
50+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
51+
-> Bitmap Index Scan on mytbl_1_pkey
52+
Index Cond: (id1 = fk.id1)
53+
-> Bitmap Heap Scan on mytbl_2 m
54+
Recheck Cond: (id1 = fk.id1)
55+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
56+
-> Bitmap Index Scan on mytbl_2_pkey
57+
Index Cond: (id1 = fk.id1)
58+
-> Bitmap Heap Scan on mytbl_3 m
59+
Recheck Cond: (id1 = fk.id1)
60+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
61+
-> Bitmap Index Scan on mytbl_3_pkey
62+
Index Cond: (id1 = fk.id1)
63+
-> Bitmap Heap Scan on mytbl_4 m
64+
Recheck Cond: (id1 = fk.id1)
65+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
66+
-> Bitmap Index Scan on mytbl_4_pkey
67+
Index Cond: (id1 = fk.id1)
68+
-> Bitmap Heap Scan on mytbl_5 m
69+
Recheck Cond: (id1 = fk.id1)
70+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
71+
-> Bitmap Index Scan on mytbl_5_pkey
72+
Index Cond: (id1 = fk.id1)
73+
-> Bitmap Heap Scan on mytbl_6 m
74+
Recheck Cond: (id1 = fk.id1)
75+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
76+
-> Bitmap Index Scan on mytbl_6_pkey
77+
Index Cond: (id1 = fk.id1)
78+
-> Bitmap Heap Scan on mytbl_7 m
79+
Recheck Cond: (id1 = fk.id1)
80+
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
81+
-> Bitmap Index Scan on mytbl_7_pkey
82+
Index Cond: (id1 = fk.id1)
83+
(43 rows)
84+
85+
/* test joint data */
86+
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
87+
FROM mytbl m JOIN fk USING(id1, id2)
88+
WHERE NOT key <@ int4range(6, end_key);
89+
tableoid | id1 | id2 | key | start_key | end_key
90+
----------+-----+-----+-----+-----------+---------
91+
mytbl_6 | 1 | 1 | 5 | |
92+
(1 row)
93+
94+
DROP SCHEMA test CASCADE;
95+
DROP EXTENSION pg_pathman CASCADE;
96+
NOTICE: drop cascades to 8 other objects
97+
DROP SCHEMA pathman CASCADE;

‎sql/pathman_join_clause.sql

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
\set VERBOSITY terse
2+
3+
CREATESCHEMApathman;
4+
CREATE EXTENSION pg_pathman SCHEMA pathman;
5+
CREATESCHEMAtest;
6+
7+
8+
/*
9+
* Test push down a join clause into child nodes of append
10+
*/
11+
12+
/* create test tables*/
13+
CREATETABLEfk (
14+
id1INTNOT NULL,
15+
id2INTNOT NULL,
16+
start_keyINT,
17+
end_keyINT,
18+
PRIMARY KEY (id1, id2));
19+
CREATETABLEmytbl (
20+
id1INTNOT NULL,
21+
id2INTNOT NULL,
22+
keyINTNOT NULL,
23+
CONSTRAINT fk_fkFOREIGN KEY (id1, id2)REFERENCES fk(id1, id2),
24+
PRIMARY KEY (id1, key));
25+
SELECTpathman.create_hash_partitions('mytbl','id1',8);
26+
27+
/* ...fill out with test data*/
28+
INSERT INTO fkVALUES (1,1);
29+
INSERT INTO mytblVALUES (1,1,5), (1,1,6);
30+
31+
/* gather statistics on test tables to have deterministic plans*/
32+
ANALYZE fk;
33+
ANALYZE mytbl;
34+
35+
/* run test queries*/
36+
EXPLAIN (COSTS OFF)/* test plan*/
37+
SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key
38+
FROM mytbl mJOIN fk USING(id1, id2)
39+
WHERE NOT key<@ int4range(6, end_key);
40+
/* test joint data*/
41+
SELECTm.tableoid::regclass, id1, id2, key, start_key, end_key
42+
FROM mytbl mJOIN fk USING(id1, id2)
43+
WHERE NOT key<@ int4range(6, end_key);
44+
45+
46+
DROPSCHEMA test CASCADE;
47+
DROP EXTENSION pg_pathman CASCADE;
48+
DROPSCHEMA pathman CASCADE;
49+

0 commit comments

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