forked from timescale/timescaledb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
parallel-10.out
464 lines (431 loc) · 20.5 KB
/
parallel-10.out
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
--parallel queries require big-ish tables so collect them all here
--so that we need to generate queries only once.
-- look at postgres version to decide whether we run with analyze or without
SELECT
CASE WHEN current_setting('server_version_num')::int >= 100000
THEN 'EXPLAIN (analyze, costs off, timing off, summary off)'
ELSE 'EXPLAIN (costs off)'
END AS "PREFIX",
'EXPLAIN (costs off)' AS "PREFIX_NO_ANALYZE"
\gset
\set CHUNK1 _timescaledb_internal._hyper_1_1_chunk
\set CHUNK2 _timescaledb_internal._hyper_1_2_chunk
CREATE TABLE test (i int, j double precision, ts timestamp);
SELECT create_hypertable('test','i',chunk_time_interval:=500000);
NOTICE: adding not-null constraint to column "i"
create_hypertable
-------------------
(1,public,test,t)
(1 row)
INSERT INTO test SELECT x, x+0.1, _timescaledb_internal.to_timestamp(x*1000) FROM generate_series(0,1000000-1,10) AS x;
ANALYZE test;
ALTER TABLE :CHUNK1 SET (parallel_workers=2);
ALTER TABLE :CHUNK2 SET (parallel_workers=2);
SET work_mem TO '50MB';
SET force_parallel_mode = 'on';
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost TO 0;
EXPLAIN (costs off) SELECT first(i, j) FROM "test";
QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Parallel Seq Scan on _hyper_1_2_chunk
(7 rows)
SELECT first(i, j) FROM "test";
first
-------
0
(1 row)
EXPLAIN (costs off) SELECT last(i, j) FROM "test";
QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Parallel Seq Scan on _hyper_1_2_chunk
(7 rows)
SELECT last(i, j) FROM "test";
last
--------
999990
(1 row)
EXPLAIN (costs off) SELECT time_bucket('1 second', ts) sec, last(i, j)
FROM "test"
GROUP BY sec
ORDER BY sec
LIMIT 5;
QUERY PLAN
--------------------------------------------------------------------------------------------
Limit
-> Sort
Sort Key: (time_bucket('@ 1 sec'::interval, _hyper_1_1_chunk.ts))
-> Finalize HashAggregate
Group Key: (time_bucket('@ 1 sec'::interval, _hyper_1_1_chunk.ts))
-> Gather
Workers Planned: 2
-> Partial HashAggregate
Group Key: time_bucket('@ 1 sec'::interval, _hyper_1_1_chunk.ts)
-> Result
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Parallel Seq Scan on _hyper_1_2_chunk
(13 rows)
-- test single copy parallel plan with parallel chunk append
-- output with analyze is not stable because it depends on worker assignment
:PREFIX_NO_ANALYZE SELECT time_bucket('1 second', ts) sec, last(i, j)
FROM "test"
WHERE length(version()) > 0
GROUP BY sec
ORDER BY sec
LIMIT 5;
QUERY PLAN
---------------------------------------------------------------------------------------
Limit
-> Sort
Sort Key: (time_bucket('@ 1 sec'::interval, test.ts))
-> Finalize HashAggregate
Group Key: (time_bucket('@ 1 sec'::interval, test.ts))
-> Gather
Workers Planned: 2
-> Partial HashAggregate
Group Key: time_bucket('@ 1 sec'::interval, test.ts)
-> Result
One-Time Filter: (length(version()) > 0)
-> Parallel Custom Scan (ChunkAppend) on test
Chunks excluded during startup: 0
-> Result
One-Time Filter: (length(version()) > 0)
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Result
One-Time Filter: (length(version()) > 0)
-> Parallel Seq Scan on _hyper_1_2_chunk
(19 rows)
SELECT time_bucket('1 second', ts) sec, last(i, j)
FROM "test"
GROUP BY sec
ORDER BY sec
LIMIT 5;
sec | last
--------------------------+------
Wed Dec 31 16:00:00 1969 | 990
Wed Dec 31 16:00:01 1969 | 1990
Wed Dec 31 16:00:02 1969 | 2990
Wed Dec 31 16:00:03 1969 | 3990
Wed Dec 31 16:00:04 1969 | 4990
(5 rows)
--test variants of histogram
EXPLAIN (costs off) SELECT histogram(i, 1, 1000000, 2) FROM "test";
QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Parallel Seq Scan on _hyper_1_2_chunk
(7 rows)
SELECT histogram(i, 1, 1000000, 2) FROM "test";
histogram
-------------------
{1,50000,49999,0}
(1 row)
EXPLAIN (costs off) SELECT histogram(i, 1,1000001,10) FROM "test";
QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Parallel Seq Scan on _hyper_1_2_chunk
(7 rows)
SELECT histogram(i, 1, 1000001, 10) FROM "test";
histogram
------------------------------------------------------------------
{1,10000,10000,10000,10000,10000,10000,10000,10000,10000,9999,0}
(1 row)
EXPLAIN (costs off) SELECT histogram(i, 0,100000,5) FROM "test";
QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Parallel Seq Scan on _hyper_1_2_chunk
(7 rows)
SELECT histogram(i, 0, 100000, 5) FROM "test";
histogram
------------------------------------
{0,2000,2000,2000,2000,2000,90000}
(1 row)
EXPLAIN (costs off) SELECT histogram(i, 10,100000,5) FROM "test";
QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
-> Parallel Seq Scan on _hyper_1_2_chunk
(7 rows)
SELECT histogram(i, 10, 100000, 5) FROM "test";
histogram
------------------------------------
{1,2000,2000,2000,2000,1999,90000}
(1 row)
EXPLAIN (costs off) SELECT histogram(NULL, 10,100000,5) FROM "test" WHERE i = coalesce(-1,j);
QUERY PLAN
------------------------------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on _hyper_1_1_chunk
Filter: ((i)::double precision = '-1'::double precision)
-> Parallel Seq Scan on _hyper_1_2_chunk
Filter: ((i)::double precision = '-1'::double precision)
(9 rows)
SELECT histogram(NULL, 10,100000,5) FROM "test" WHERE i = coalesce(-1,j);
histogram
-----------
(1 row)
-- test parallel ChunkAppend
:PREFIX SELECT i FROM "test" WHERE length(version()) > 0;
QUERY PLAN
----------------------------------------------------------------------------------
Gather (actual rows=100000 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Result (actual rows=100000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Custom Scan (ChunkAppend) on test (actual rows=100000 loops=1)
Chunks excluded during startup: 0
-> Result (actual rows=50000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Seq Scan on _hyper_1_1_chunk (actual rows=50000 loops=1)
-> Result (actual rows=50000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Seq Scan on _hyper_1_2_chunk (actual rows=50000 loops=1)
(14 rows)
-- test worker assignment
-- first chunk should have 1 worker and second chunk should have 2
SET max_parallel_workers_per_gather TO 2;
:PREFIX SELECT count(*) FROM "test" WHERE i >= 400000 AND length(version()) > 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (actual rows=1 loops=3)
-> Result (actual rows=20000 loops=3)
One-Time Filter: (length(version()) > 0)
-> Parallel Custom Scan (ChunkAppend) on test (actual rows=20000 loops=3)
Chunks excluded during startup: 0
-> Result (actual rows=10000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Parallel Index Only Scan using _hyper_1_1_chunk_test_i_idx on _hyper_1_1_chunk (actual rows=10000 loops=1)
Index Cond: (i >= 400000)
Heap Fetches: 0
-> Result (actual rows=25000 loops=2)
One-Time Filter: (length(version()) > 0)
-> Parallel Seq Scan on _hyper_1_2_chunk (actual rows=25000 loops=2)
Filter: (i >= 400000)
(18 rows)
SELECT count(*) FROM "test" WHERE i >= 400000 AND length(version()) > 0;
count
-------
60000
(1 row)
-- test worker assignment
-- first chunk should have 2 worker and second chunk should have 1
:PREFIX SELECT count(*) FROM "test" WHERE i < 600000 AND length(version()) > 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (actual rows=1 loops=3)
-> Result (actual rows=20000 loops=3)
One-Time Filter: (length(version()) > 0)
-> Parallel Custom Scan (ChunkAppend) on test (actual rows=20000 loops=3)
Chunks excluded during startup: 0
-> Result (actual rows=25000 loops=2)
One-Time Filter: (length(version()) > 0)
-> Parallel Seq Scan on _hyper_1_1_chunk (actual rows=25000 loops=2)
Filter: (i < 600000)
-> Result (actual rows=10000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Parallel Index Only Scan using _hyper_1_2_chunk_test_i_idx on _hyper_1_2_chunk (actual rows=10000 loops=1)
Index Cond: (i < 600000)
Heap Fetches: 0
(18 rows)
SELECT count(*) FROM "test" WHERE i < 600000 AND length(version()) > 0;
count
-------
60000
(1 row)
-- test ChunkAppend with # workers < # childs
SET max_parallel_workers_per_gather TO 1;
:PREFIX SELECT count(*) FROM "test" WHERE length(version()) > 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=2 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Partial Aggregate (actual rows=1 loops=2)
-> Result (actual rows=50000 loops=2)
One-Time Filter: (length(version()) > 0)
-> Parallel Custom Scan (ChunkAppend) on test (actual rows=50000 loops=2)
Chunks excluded during startup: 0
-> Result (actual rows=50000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Parallel Seq Scan on _hyper_1_1_chunk (actual rows=50000 loops=1)
-> Result (actual rows=50000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Parallel Seq Scan on _hyper_1_2_chunk (actual rows=50000 loops=1)
(15 rows)
SELECT count(*) FROM "test" WHERE length(version()) > 0;
count
--------
100000
(1 row)
-- test ChunkAppend with # workers > # childs
SET max_parallel_workers_per_gather TO 2;
:PREFIX SELECT count(*) FROM "test" WHERE i >= 500000 AND length(version()) > 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (actual rows=1 loops=3)
-> Result (actual rows=16667 loops=3)
One-Time Filter: (length(version()) > 0)
-> Parallel Custom Scan (ChunkAppend) on test (actual rows=16667 loops=3)
Chunks excluded during startup: 0
-> Result (actual rows=25000 loops=2)
One-Time Filter: (length(version()) > 0)
-> Parallel Seq Scan on _hyper_1_2_chunk (actual rows=25000 loops=2)
Filter: (i >= 500000)
(13 rows)
SELECT count(*) FROM "test" WHERE i >= 500000 AND length(version()) > 0;
count
-------
50000
(1 row)
RESET max_parallel_workers_per_gather;
-- test partial and non-partial plans
-- these will not be parallel on PG < 11
ALTER TABLE :CHUNK1 SET (parallel_workers=0);
ALTER TABLE :CHUNK2 SET (parallel_workers=2);
:PREFIX SELECT count(*) FROM "test" WHERE i > 400000 AND length(version()) > 0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather (actual rows=1 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Aggregate (actual rows=1 loops=1)
-> Result (actual rows=59999 loops=1)
One-Time Filter: (length(version()) > 0)
-> Custom Scan (ChunkAppend) on test (actual rows=59999 loops=1)
Chunks excluded during startup: 0
-> Result (actual rows=9999 loops=1)
One-Time Filter: (length(version()) > 0)
-> Index Only Scan using _hyper_1_1_chunk_test_i_idx on _hyper_1_1_chunk (actual rows=9999 loops=1)
Index Cond: (i > 400000)
Heap Fetches: 0
-> Result (actual rows=50000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Seq Scan on _hyper_1_2_chunk (actual rows=50000 loops=1)
Filter: (i > 400000)
(18 rows)
ALTER TABLE :CHUNK1 SET (parallel_workers=2);
ALTER TABLE :CHUNK2 SET (parallel_workers=0);
:PREFIX SELECT count(*) FROM "test" WHERE i < 600000 AND length(version()) > 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather (actual rows=1 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Aggregate (actual rows=1 loops=1)
-> Result (actual rows=60000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Custom Scan (ChunkAppend) on test (actual rows=60000 loops=1)
Chunks excluded during startup: 0
-> Result (actual rows=50000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Seq Scan on _hyper_1_1_chunk (actual rows=50000 loops=1)
Filter: (i < 600000)
-> Result (actual rows=10000 loops=1)
One-Time Filter: (length(version()) > 0)
-> Index Only Scan using _hyper_1_2_chunk_test_i_idx on _hyper_1_2_chunk (actual rows=10000 loops=1)
Index Cond: (i < 600000)
Heap Fetches: 0
(18 rows)
ALTER TABLE :CHUNK1 RESET (parallel_workers);
ALTER TABLE :CHUNK2 RESET (parallel_workers);
-- now() is not marked parallel safe in PostgreSQL < 12 so using now()
-- in a query will prevent parallelism but CURRENT_TIMESTAMP and
-- transaction_timestamp() are marked parallel safe
:PREFIX SELECT i FROM "test" WHERE ts < CURRENT_TIMESTAMP;
QUERY PLAN
----------------------------------------------------------------------
Gather (actual rows=100000 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Custom Scan (ChunkAppend) on test (actual rows=100000 loops=1)
Chunks excluded during startup: 0
-> Seq Scan on _hyper_1_1_chunk (actual rows=50000 loops=1)
Filter: (ts < CURRENT_TIMESTAMP)
-> Seq Scan on _hyper_1_2_chunk (actual rows=50000 loops=1)
Filter: (ts < CURRENT_TIMESTAMP)
(10 rows)
:PREFIX SELECT i FROM "test" WHERE ts < transaction_timestamp();
QUERY PLAN
----------------------------------------------------------------------
Gather (actual rows=100000 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Custom Scan (ChunkAppend) on test (actual rows=100000 loops=1)
Chunks excluded during startup: 0
-> Seq Scan on _hyper_1_1_chunk (actual rows=50000 loops=1)
Filter: (ts < transaction_timestamp())
-> Seq Scan on _hyper_1_2_chunk (actual rows=50000 loops=1)
Filter: (ts < transaction_timestamp())
(10 rows)
-- this won't be parallel query because now() is parallel restricted in PG < 12
:PREFIX SELECT i FROM "test" WHERE ts < now();
QUERY PLAN
----------------------------------------------------------------
Custom Scan (ChunkAppend) on test (actual rows=100000 loops=1)
Chunks excluded during startup: 0
-> Seq Scan on _hyper_1_1_chunk (actual rows=50000 loops=1)
Filter: (ts < now())
-> Seq Scan on _hyper_1_2_chunk (actual rows=50000 loops=1)
Filter: (ts < now())
(6 rows)