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

Commit0eaeac8

Browse files
committed
Add materialization and force GUC
1 parentad210e6 commit0eaeac8

File tree

3 files changed

+101
-48
lines changed

3 files changed

+101
-48
lines changed

‎contrib/tempscan/expected/basic.out

Lines changed: 74 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@ INSERT INTO parallel_test (x) SELECT x FROM generate_series(1,100) AS x;
1717
CREATE TEMP TABLE parallel_test_tmp AS (SELECT * FROM parallel_test);
1818
VACUUM ANALYZE parallel_test, parallel_test_tmp;
1919
SET tempscan.enable = 'on';
20+
SET tempscan.force = 'on';
2021
EXPLAIN (COSTS OFF)
2122
SELECT count(*) FROM parallel_test;
2223
QUERY PLAN
@@ -56,8 +57,8 @@ SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test t2;
5657

5758
EXPLAIN (COSTS OFF)
5859
SELECT count(*) FROM parallel_test_tmp t1 NATURAL JOIN parallel_test t2;
59-
QUERY PLAN
60-
----------------------------------------------------------------------
60+
QUERY PLAN
61+
----------------------------------------------------------------------------
6162
Finalize Aggregate
6263
-> Gather
6364
Workers Planned: 1
@@ -66,9 +67,10 @@ SELECT count(*) FROM parallel_test_tmp t1 NATURAL JOIN parallel_test t2;
6667
Hash Cond: (t2.x = t1.x)
6768
-> Parallel Seq Scan on parallel_test t2
6869
-> Hash
69-
-> Custom Scan (nodeCustomTempScan)
70-
-> Seq Scan on parallel_test_tmp t1
71-
(10 rows)
70+
-> Materialize
71+
-> Custom Scan (nodeCustomTempScan)
72+
-> Seq Scan on parallel_test_tmp t1
73+
(11 rows)
7274

7375
-- Just see how merge join manages custom parallel scan path
7476
SET enable_hashjoin = 'off';
@@ -92,8 +94,8 @@ SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test t2;
9294

9395
EXPLAIN (COSTS OFF)
9496
SELECT count(*) FROM parallel_test_tmp t1 NATURAL JOIN parallel_test t2;
95-
QUERY PLAN
96-
----------------------------------------------------------------------
97+
QUERY PLAN
98+
----------------------------------------------------------------------------
9799
Finalize Aggregate
98100
-> Gather
99101
Workers Planned: 1
@@ -105,9 +107,10 @@ SELECT count(*) FROM parallel_test_tmp t1 NATURAL JOIN parallel_test t2;
105107
-> Parallel Seq Scan on parallel_test t2
106108
-> Sort
107109
Sort Key: t1.x
108-
-> Custom Scan (nodeCustomTempScan)
109-
-> Seq Scan on parallel_test_tmp t1
110-
(13 rows)
110+
-> Materialize
111+
-> Custom Scan (nodeCustomTempScan)
112+
-> Seq Scan on parallel_test_tmp t1
113+
(14 rows)
111114

112115
RESET enable_hashjoin;
113116
-- Increase table size and see how indexes work
@@ -119,18 +122,20 @@ VACUUM ANALYZE;
119122
EXPLAIN (COSTS OFF)
120123
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp t2
121124
WHERE t1.x < 10;
122-
QUERY PLAN
123-
-----------------------------------------------------------------------------------------------
125+
QUERY PLAN
126+
----------------------------------------------------------------------
124127
Aggregate
125128
-> Gather
126129
Workers Planned: 3
127-
-> Nested Loop
130+
-> Hash Join
131+
Hash Cond: (t1.x = t2.x)
128132
-> Parallel Seq Scan on parallel_test t1
129133
Filter: (x < 10)
130-
-> Custom Scan (nodeCustomTempScan)
131-
-> Index Only Scan using parallel_test_tmp_x_idx on parallel_test_tmp t2
132-
Index Cond: (x = t1.x)
133-
(9 rows)
134+
-> Hash
135+
-> Materialize
136+
-> Custom Scan (nodeCustomTempScan)
137+
-> Seq Scan on parallel_test_tmp t2
138+
(11 rows)
134139

135140
CREATE TEMP TABLE parallel_test_tmp_2 AS (SELECT * FROM parallel_test);
136141
CREATE INDEX ON parallel_test_tmp_2 (x);
@@ -155,42 +160,61 @@ WHERE t2.x < 10;
155160
EXPLAIN (COSTS OFF)
156161
SELECT count(*) FROM parallel_test_tmp_2 t1 NATURAL JOIN parallel_test_tmp_2 t2
157162
WHERE t1.x < 10;
158-
QUERY PLAN
159-
----------------------------------------------------------------------------------------
163+
QUERY PLAN
164+
----------------------------------------------------------------------------------------------
160165
Aggregate
161166
-> Nested Loop
162-
->Custom Scan (nodeCustomTempScan)
163-
->Index Scanusing parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t1
164-
IndexCond: (x < 10)
165-
-> Custom Scan (nodeCustomTempScan)
166-
-> Index Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t2
167-
Index Cond: (x = t1.x)
168-
Filter: (y = t1.y)
167+
->Materialize
168+
->Custom Scan(nodeCustomTempScan)
169+
->IndexScan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t1
170+
Index Cond: (x < 10)
171+
-> Index Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t2
172+
Index Cond: (x = t1.x)
173+
Filter: (y = t1.y)
169174
(9 rows)
170175

171176
-- Employ parallel join using CustomScan as an inner
172177
EXPLAIN (COSTS OFF)
173178
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2
174-
WHERE t1.x < 10;
175-
QUERY PLAN
176-
----------------------------------------------------------------------------------------------
179+
WHERE t1.x < 10 AND t2.x < 10;
180+
QUERY PLAN
181+
----------------------------------------------------------------------------------------------------------
177182
Aggregate
178183
-> Gather
179184
Workers Planned: 3
180-
-> Nested Loop
185+
-> Hash Join
186+
Hash Cond: ((t1.x = t2.x) AND (t1.y = t2.y))
181187
-> Parallel Seq Scan on parallel_test t1
182188
Filter: (x < 10)
183-
-> Custom Scan (nodeCustomTempScan)
184-
-> Index Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t2
185-
Index Cond: (x = t1.x)
186-
Filter: (t1.y = y)
187-
(10 rows)
189+
-> Hash
190+
-> Materialize
191+
-> Custom Scan (nodeCustomTempScan)
192+
-> Index Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t2
193+
Index Cond: (x < 10)
194+
(12 rows)
195+
196+
-- Parameterised NestLoop beats HashJoin. No ParallelTempScan possible
197+
EXPLAIN (COSTS OFF)
198+
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2
199+
WHERE t1.x < 10;
200+
QUERY PLAN
201+
----------------------------------------------------------------------------------
202+
Aggregate
203+
-> Nested Loop
204+
-> Gather
205+
Workers Planned: 3
206+
-> Parallel Seq Scan on parallel_test t1
207+
Filter: (x < 10)
208+
-> Index Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t2
209+
Index Cond: (x = t1.x)
210+
Filter: (t1.y = y)
211+
(9 rows)
188212

189213
-- Check real execution
190214
EXPLAIN (VERBOSE, COSTS OFF)
191215
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
192-
QUERY PLAN
193-
--------------------------------------------------------------------------
216+
QUERY PLAN
217+
--------------------------------------------------------------------------------
194218
Aggregate
195219
Output: count(*)
196220
-> Gather
@@ -201,11 +225,13 @@ SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
201225
Output: t1.x, t1.y
202226
-> Hash
203227
Output: t2.x, t2.y
204-
->Custom Scan (nodeCustomTempScan)
228+
->Materialize
205229
Output: t2.x, t2.y
206-
->Seq Scanon pg_temp.parallel_test_tmp_2 t2
230+
->Custom Scan(nodeCustomTempScan)
207231
Output: t2.x, t2.y
208-
(14 rows)
232+
-> Seq Scan on pg_temp.parallel_test_tmp_2 t2
233+
Output: t2.x, t2.y
234+
(16 rows)
209235

210236
SET max_parallel_workers_per_gather = 0;
211237
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
@@ -217,8 +243,8 @@ SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
217243
SET max_parallel_workers_per_gather = 3;
218244
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
219245
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
220-
QUERY PLAN
221-
----------------------------------------------------------------------------------------------
246+
QUERY PLAN
247+
------------------------------------------------------------------------------------------------------
222248
Aggregate (actual rows=1 loops=1)
223249
-> Gather (actual rows=10100 loops=1)
224250
Workers Planned: 3
@@ -227,16 +253,18 @@ SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
227253
Hash Cond: ((t1.x = t2.x) AND (t1.y = t2.y))
228254
-> Parallel Seq Scan on parallel_test t1 (actual rows=2525 loops=4)
229255
-> Hash (actual rows=10100 loops=4)
230-
Buckets: 16384 Batches: 1 Memory Usage: 571kB
231-
-> Custom Scan (nodeCustomTempScan) (actual rows=10100 loops=4)
232-
-> Seq Scan on parallel_test_tmp_2 t2 (actual rows=10100 loops=1)
233-
(11 rows)
256+
Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 571kB
257+
-> Materialize (actual rows=10100 loops=4)
258+
-> Custom Scan (nodeCustomTempScan) (actual rows=10100 loops=4)
259+
-> Seq Scan on parallel_test_tmp_2 t2 (actual rows=10100 loops=1)
260+
(12 rows)
234261

235262
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
236263
count
237264
-------
238265
10100
239266
(1 row)
240267

268+
RESET tempscan.force;
241269
RESET tempscan.enable;
242270
DROP TABLE parallel_test, parallel_test_tmp;

‎contrib/tempscan/nodeCustomTempScan.c

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,7 @@ static CustomExecMethods exec_methods =
101101
staticset_rel_pathlist_hook_typeset_rel_pathlist_hook_next=NULL;
102102

103103
staticbooltempscan_enable= true;
104+
staticbooltempscan_force= false;
104105

105106
void_PG_init(void);
106107

@@ -411,7 +412,7 @@ try_partial_tempscan(PlannerInfo *root, RelOptInfo *rel, Index rti,
411412
if (set_rel_pathlist_hook_next)
412413
(*set_rel_pathlist_hook_next)(root,rel,rti,rte);
413414

414-
if (!tempscan_enable||rel->consider_parallel||rel->lateral_relids)
415+
if (!tempscan_enable||rel->consider_parallel)
415416
return;
416417

417418
if (rte->rtekind!=RTE_RELATION||rel->reloptkind!=RELOPT_BASEREL||
@@ -470,11 +471,16 @@ try_partial_tempscan(PlannerInfo *root, RelOptInfo *rel, Index rti,
470471
Path*path=lfirst(lc);
471472
Path*cpath;
472473

473-
if (!path->parallel_safe)
474+
if (!path->parallel_safe||path->param_info!=NULL)
474475
continue;
475476

477+
478+
path->rows=clamp_row_est(path->rows /3.);
476479
cpath= (Path*)create_partial_tempscan_path(root,rel,path);
477480

481+
if (tempscan_force)
482+
path->total_cost=clamp_row_est(path->total_cost /disable_cost);
483+
478484
/*
479485
* Need materialisation here. Do the absence of internal parameters and
480486
* lateral references guarantees we don't need to change any parameters
@@ -502,6 +508,18 @@ _PG_init(void)
502508
NULL
503509
);
504510

511+
DefineCustomBoolVariable("tempscan.force",
512+
NULL,
513+
NULL,
514+
&tempscan_force,
515+
false,
516+
PGC_SUSET,
517+
0,
518+
NULL,
519+
NULL,
520+
NULL
521+
);
522+
505523
set_rel_pathlist_hook_next=set_rel_pathlist_hook;
506524
set_rel_pathlist_hook=try_partial_tempscan;
507525

‎contrib/tempscan/sql/basic.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@ CREATE TEMP TABLE parallel_test_tmp AS (SELECT * FROM parallel_test);
2222
VACUUM ANALYZE parallel_test, parallel_test_tmp;
2323

2424
SETtempscan.enable='on';
25+
SETtempscan.force='on';
2526
EXPLAIN (COSTS OFF)
2627
SELECTcount(*)FROM parallel_test;
2728

@@ -70,7 +71,12 @@ WHERE t1.x < 10;
7071
-- Employ parallel join using CustomScan as an inner
7172
EXPLAIN (COSTS OFF)
7273
SELECTcount(*)FROM parallel_test t1NATURAL JOIN parallel_test_tmp_2 t2
74+
WHEREt1.x<10ANDt2.x<10;
75+
-- Parameterised NestLoop beats HashJoin. No ParallelTempScan possible
76+
EXPLAIN (COSTS OFF)
77+
SELECTcount(*)FROM parallel_test t1NATURAL JOIN parallel_test_tmp_2 t2
7378
WHEREt1.x<10;
79+
7480
-- Check real execution
7581
EXPLAIN (VERBOSE, COSTS OFF)
7682
SELECTcount(*)FROM parallel_test t1NATURAL JOIN parallel_test_tmp_2 t2;
@@ -81,5 +87,6 @@ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
8187
SELECTcount(*)FROM parallel_test t1NATURAL JOIN parallel_test_tmp_2 t2;
8288
SELECTcount(*)FROM parallel_test t1NATURAL JOIN parallel_test_tmp_2 t2;
8389

90+
RESETtempscan.force;
8491
RESETtempscan.enable;
8592
DROPTABLE parallel_test, parallel_test_tmp;

0 commit comments

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