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

Commit7e13f7d

Browse files
committed
Add tests on index scan utilisation and minor fix on the issue found out
during the testing.
1 parent7b2029d commit7e13f7d

File tree

3 files changed

+102
-2
lines changed

3 files changed

+102
-2
lines changed

‎contrib/tempscan/expected/basic.out

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -113,5 +113,82 @@ SELECT count(*) FROM parallel_test_tmp t1 NATURAL JOIN parallel_test t2;
113113
(13 rows)
114114

115115
RESET enable_hashjoin;
116+
-- Increase table size and see how indexes work
117+
ALTER TABLE parallel_test ADD COLUMN y text DEFAULT 'none';
118+
INSERT INTO parallel_test (x,y) SELECT x, 'data' || x AS y FROM generate_series(1,10000) AS x;
119+
CREATE INDEX ON parallel_test (x);
120+
ANALYZE parallel_test;
121+
EXPLAIN (COSTS OFF)
122+
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test t2
123+
WHERE t1.x < 10;
124+
QUERY PLAN
125+
-------------------------------------------------------------------------------------
126+
Aggregate
127+
-> Gather
128+
Workers Planned: 1
129+
-> Nested Loop
130+
-> Parallel Index Scan using parallel_test_x_idx on parallel_test t1
131+
Index Cond: (x < 10)
132+
-> Index Scan using parallel_test_x_idx on parallel_test t2
133+
Index Cond: (x = t1.x)
134+
Filter: (y = t1.y)
135+
(9 rows)
136+
137+
EXPLAIN (COSTS OFF)
138+
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp t2
139+
WHERE t1.x < 10;
140+
QUERY PLAN
141+
---------------------------------------------------------------------------------
142+
Aggregate
143+
-> Gather
144+
Workers Planned: 1
145+
-> Merge Join
146+
Merge Cond: (t2.x = t1.x)
147+
-> Sort
148+
Sort Key: t2.x
149+
-> Custom Scan (nodeCustomTempScan)
150+
-> Parallel Seq Scan on parallel_test_tmp t2
151+
-> Index Only Scan using parallel_test_x_idx on parallel_test t1
152+
Index Cond: (x < 10)
153+
(11 rows)
154+
155+
CREATE TEMP TABLE parallel_test_tmp_2 AS (SELECT * FROM parallel_test);
156+
CREATE INDEX ON parallel_test_tmp_2 (x);
157+
ANALYZE parallel_test_tmp_2;
158+
EXPLAIN (COSTS OFF)
159+
SELECT count(*) FROM parallel_test_tmp t1 NATURAL JOIN parallel_test_tmp_2 t2
160+
WHERE t2.x < 10;
161+
QUERY PLAN
162+
---------------------------------------------------------------------------------------------
163+
Aggregate
164+
-> Gather
165+
Workers Planned: 1
166+
-> Merge Join
167+
Merge Cond: (t1.x = t2.x)
168+
-> Sort
169+
Sort Key: t1.x
170+
-> Custom Scan (nodeCustomTempScan)
171+
-> Parallel Seq Scan on parallel_test_tmp t1
172+
-> Index Only Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t2
173+
Index Cond: (x < 10)
174+
(11 rows)
175+
176+
EXPLAIN (COSTS OFF)
177+
SELECT count(*) FROM parallel_test_tmp_2 t1 NATURAL JOIN parallel_test_tmp_2 t2
178+
WHERE t1.x < 10;
179+
QUERY PLAN
180+
-------------------------------------------------------------------------------------------------------
181+
Aggregate
182+
-> Gather
183+
Workers Planned: 1
184+
-> Nested Loop
185+
-> Custom Scan (nodeCustomTempScan)
186+
-> Parallel Index Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t1
187+
Index Cond: (x < 10)
188+
-> Index Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t2
189+
Index Cond: (x = t1.x)
190+
Filter: (y = t1.y)
191+
(10 rows)
192+
116193
RESET tempscan.enable;
117194
DROP TABLE parallel_test, parallel_test_tmp;

‎contrib/tempscan/nodeCustomTempScan.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -283,5 +283,7 @@ _PG_init(void)
283283
set_rel_pathlist_hook_next=set_rel_pathlist_hook;
284284
set_rel_pathlist_hook=try_partial_tempscan;
285285

286+
RegisterCustomScanMethods(&plan_methods);
287+
286288
MarkGUCPrefixReserved(MODULENAME);
287289
}

‎contrib/tempscan/sql/basic.sql

Lines changed: 23 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -42,8 +42,29 @@ SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test t2;
4242
EXPLAIN (COSTS OFF)
4343
SELECTcount(*)FROM parallel_test_tmp t1NATURAL JOIN parallel_test t2;
4444

45-
-- TODO: Tests on parallel index scan
46-
4745
RESET enable_hashjoin;
46+
47+
-- Increase table size and see how indexes work
48+
ALTERTABLE parallel_test ADD COLUMN ytext DEFAULT'none';
49+
INSERT INTO parallel_test (x,y)SELECT x,'data'|| xAS yFROM generate_series(1,10000)AS x;
50+
CREATEINDEXON parallel_test (x);
51+
ANALYZE parallel_test;
52+
EXPLAIN (COSTS OFF)
53+
SELECTcount(*)FROM parallel_test t1NATURAL JOIN parallel_test t2
54+
WHEREt1.x<10;
55+
EXPLAIN (COSTS OFF)
56+
SELECTcount(*)FROM parallel_test t1NATURAL JOIN parallel_test_tmp t2
57+
WHEREt1.x<10;
58+
59+
CREATE TEMP TABLE parallel_test_tmp_2AS (SELECT*FROM parallel_test);
60+
CREATEINDEXON parallel_test_tmp_2 (x);
61+
ANALYZE parallel_test_tmp_2;
62+
EXPLAIN (COSTS OFF)
63+
SELECTcount(*)FROM parallel_test_tmp t1NATURAL JOIN parallel_test_tmp_2 t2
64+
WHEREt2.x<10;
65+
EXPLAIN (COSTS OFF)
66+
SELECTcount(*)FROM parallel_test_tmp_2 t1NATURAL JOIN parallel_test_tmp_2 t2
67+
WHEREt1.x<10;
68+
4869
RESETtempscan.enable;
4970
DROPTABLE parallel_test, parallel_test_tmp;

0 commit comments

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