Skip to content

Commit 0eaeac8

Browse files
danolivodanolivo
committed
Add materialization and force GUC
1 parent ad210e6 commit 0eaeac8

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 Scan using parallel_test_tmp_2_x_idx on parallel_test_tmp_2 t1
164-
Index Cond: (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+
-> Index Scan 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 Scan on 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
static set_rel_pathlist_hook_type set_rel_pathlist_hook_next = NULL;
102102

103103
static bool tempscan_enable = true;
104+
static bool tempscan_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
SET tempscan.enable = 'on';
25+
SET tempscan.force = 'on';
2526
EXPLAIN (COSTS OFF)
2627
SELECT count(*) 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
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2
74+
WHERE t1.x < 10 AND t2.x < 10;
75+
-- Parameterised NestLoop beats HashJoin. No ParallelTempScan possible
76+
EXPLAIN (COSTS OFF)
77+
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2
7378
WHERE t1.x < 10;
79+
7480
-- Check real execution
7581
EXPLAIN (VERBOSE, COSTS OFF)
7682
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
@@ -81,5 +87,6 @@ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
8187
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
8288
SELECT count(*) FROM parallel_test t1 NATURAL JOIN parallel_test_tmp_2 t2;
8389

90+
RESET tempscan.force;
8491
RESET tempscan.enable;
8592
DROP TABLE parallel_test, parallel_test_tmp;

0 commit comments

Comments
 (0)