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

Commite0c04c3

Browse files
committed
add test case for issue #91
1 parentd0606bf commite0c04c3

File tree

2 files changed

+145
-13
lines changed

2 files changed

+145
-13
lines changed

‎expected/pathman_join_clause.out

Lines changed: 85 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -28,15 +28,14 @@ SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);
2828
INSERT INTO test.fk VALUES (1, 1);
2929
INSERT INTO test.mytbl VALUES (1, 1, 5), (1, 1, 6);
3030
/* gather statistics on test tables to have deterministic plans */
31-
ANALYZE test.fk;
32-
ANALYZE test.mytbl;
31+
ANALYZE;
3332
/* run test queries */
3433
EXPLAIN (COSTS OFF) /* test plan */
3534
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
3635
FROM test.mytbl m JOIN test.fk USING(id1, id2)
3736
WHERE NOT key <@ int4range(6, end_key);
38-
QUERY PLAN
39-
------------------------------------------------------------------------------------
37+
QUERY PLAN
38+
-------------------------------------------------------------------------------------------------------
4039
Nested Loop
4140
-> Seq Scan on fk
4241
-> Custom Scan (RuntimeAppend)
@@ -71,17 +70,14 @@ WHERE NOT key <@ int4range(6, end_key);
7170
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
7271
-> Bitmap Index Scan on mytbl_5_pkey
7372
Index Cond: (id1 = fk.id1)
74-
-> Bitmap Heap Scan on mytbl_6 m
75-
Recheck Cond: (id1 = fk.id1)
76-
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
77-
-> Bitmap Index Scan on mytbl_6_pkey
78-
Index Cond: (id1 = fk.id1)
73+
-> Seq Scan on mytbl_6 m
74+
Filter: ((fk.id1 = id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
7975
-> Bitmap Heap Scan on mytbl_7 m
8076
Recheck Cond: (id1 = fk.id1)
8177
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
8278
-> Bitmap Index Scan on mytbl_7_pkey
8379
Index Cond: (id1 = fk.id1)
84-
(44 rows)
80+
(41 rows)
8581

8682
/* test joint data */
8783
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
@@ -92,7 +88,85 @@ WHERE NOT key <@ int4range(6, end_key);
9288
test.mytbl_6 | 1 | 1 | 5 | |
9389
(1 row)
9490

91+
/*
92+
* Test case by @dimarick
93+
*/
94+
CREATE TABLE test.parent (
95+
id SERIAL NOT NULL,
96+
owner_id INTEGER NOT NULL
97+
);
98+
CREATE TABLE test.child (
99+
parent_id INTEGER NOT NULL,
100+
owner_id INTEGER NOT NULL
101+
);
102+
CREATE TABLE test.child_nopart (
103+
parent_id INTEGER NOT NULL,
104+
owner_id INTEGER NOT NULL
105+
);
106+
INSERT INTO test.parent (owner_id) VALUES (1), (2), (3), (3);
107+
INSERT INTO test.child (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3);
108+
INSERT INTO test.child_nopart (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3);
109+
SELECT pathman.create_hash_partitions('test.child', 'owner_id', 2);
110+
create_hash_partitions
111+
------------------------
112+
2
113+
(1 row)
114+
115+
/* gather statistics on test tables to have deterministic plans */
116+
ANALYZE;
117+
/* Query #1 */
118+
EXPLAIN (COSTS OFF) SELECT * FROM test.parent
119+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
120+
test.child.owner_id = test.parent.owner_id
121+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
122+
QUERY PLAN
123+
-----------------------------------------------------------------------------------------------------
124+
Nested Loop Left Join
125+
-> Seq Scan on parent
126+
Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3))
127+
-> Custom Scan (RuntimeAppend)
128+
Prune by: ((child.owner_id = 3) AND (child.owner_id = parent.owner_id))
129+
-> Seq Scan on child_1 child
130+
Filter: ((owner_id = 3) AND (owner_id = parent.owner_id) AND (parent_id = parent.id))
131+
(7 rows)
132+
133+
SELECT * FROM test.parent
134+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
135+
test.child.owner_id = test.parent.owner_id
136+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
137+
id | owner_id | parent_id | owner_id
138+
----+----------+-----------+----------
139+
3 | 3 | 3 | 3
140+
4 | 3 | |
141+
(2 rows)
142+
143+
/* Query #2 */
144+
EXPLAIN (COSTS OFF) SELECT * FROM test.parent
145+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
146+
test.child.owner_id = 3
147+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
148+
QUERY PLAN
149+
----------------------------------------------------------------------
150+
Nested Loop Left Join
151+
Join Filter: (child_1.parent_id = parent.id)
152+
-> Seq Scan on parent
153+
Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3))
154+
-> Append
155+
-> Seq Scan on child_1
156+
Filter: (owner_id = 3)
157+
(7 rows)
158+
159+
SELECT * FROM test.parent
160+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
161+
test.child.owner_id = 3
162+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
163+
id | owner_id | parent_id | owner_id
164+
----+----------+-----------+----------
165+
3 | 3 | 3 | 3
166+
4 | 3 | |
167+
(2 rows)
168+
95169
DROP SCHEMA test CASCADE;
96-
NOTICE: drop cascades to10 other objects
170+
NOTICE: drop cascades to15 other objects
97171
DROP EXTENSION pg_pathman CASCADE;
98172
DROP SCHEMA pathman CASCADE;

‎sql/pathman_join_clause.sql

Lines changed: 60 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@ CREATE EXTENSION pg_pathman SCHEMA pathman;
55
CREATESCHEMAtest;
66

77

8+
89
/*
910
* Test push down a join clause into child nodes of append
1011
*/
@@ -16,21 +17,23 @@ CREATE TABLE test.fk (
1617
start_keyINT,
1718
end_keyINT,
1819
PRIMARY KEY (id1, id2));
20+
1921
CREATETABLEtest.mytbl (
2022
id1INTNOT NULL,
2123
id2INTNOT NULL,
2224
keyINTNOT NULL,
2325
CONSTRAINT fk_fkFOREIGN KEY (id1, id2)REFERENCEStest.fk(id1, id2),
2426
PRIMARY KEY (id1, key));
27+
2528
SELECTpathman.create_hash_partitions('test.mytbl','id1',8);
2629

2730
/* ...fill out with test data*/
2831
INSERT INTOtest.fkVALUES (1,1);
2932
INSERT INTOtest.mytblVALUES (1,1,5), (1,1,6);
3033

3134
/* gather statistics on test tables to have deterministic plans*/
32-
ANALYZEtest.fk;
33-
ANALYZEtest.mytbl;
35+
ANALYZE;
36+
3437

3538
/* run test queries*/
3639
EXPLAIN (COSTS OFF)/* test plan*/
@@ -44,6 +47,61 @@ FROM test.mytbl m JOIN test.fk USING(id1, id2)
4447
WHERE NOT key<@ int4range(6, end_key);
4548

4649

50+
51+
/*
52+
* Test case by @dimarick
53+
*/
54+
55+
CREATETABLEtest.parent (
56+
idSERIALNOT NULL,
57+
owner_idINTEGERNOT NULL
58+
);
59+
60+
CREATETABLEtest.child (
61+
parent_idINTEGERNOT NULL,
62+
owner_idINTEGERNOT NULL
63+
);
64+
65+
CREATETABLEtest.child_nopart (
66+
parent_idINTEGERNOT NULL,
67+
owner_idINTEGERNOT NULL
68+
);
69+
70+
INSERT INTOtest.parent (owner_id)VALUES (1), (2), (3), (3);
71+
INSERT INTOtest.child (parent_id, owner_id)VALUES (1,1), (2,2), (3,3), (5,3);
72+
INSERT INTOtest.child_nopart (parent_id, owner_id)VALUES (1,1), (2,2), (3,3), (5,3);
73+
74+
SELECTpathman.create_hash_partitions('test.child','owner_id',2);
75+
76+
/* gather statistics on test tables to have deterministic plans*/
77+
ANALYZE;
78+
79+
80+
/* Query #1*/
81+
EXPLAIN (COSTS OFF)SELECT*FROMtest.parent
82+
LEFT JOINtest.childONtest.child.parent_id=test.parent.idAND
83+
test.child.owner_id=test.parent.owner_id
84+
WHEREtest.parent.owner_id=3andtest.parent.idIN (3,4);
85+
86+
SELECT*FROMtest.parent
87+
LEFT JOINtest.childONtest.child.parent_id=test.parent.idAND
88+
test.child.owner_id=test.parent.owner_id
89+
WHEREtest.parent.owner_id=3andtest.parent.idIN (3,4);
90+
91+
92+
/* Query #2*/
93+
EXPLAIN (COSTS OFF)SELECT*FROMtest.parent
94+
LEFT JOINtest.childONtest.child.parent_id=test.parent.idAND
95+
test.child.owner_id=3
96+
WHEREtest.parent.owner_id=3andtest.parent.idIN (3,4);
97+
98+
SELECT*FROMtest.parent
99+
LEFT JOINtest.childONtest.child.parent_id=test.parent.idAND
100+
test.child.owner_id=3
101+
WHEREtest.parent.owner_id=3andtest.parent.idIN (3,4);
102+
103+
104+
47105
DROPSCHEMA test CASCADE;
48106
DROP EXTENSION pg_pathman CASCADE;
49107
DROPSCHEMA pathman CASCADE;

0 commit comments

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