Skip to content

Instantly share code, notes, and snippets.

@Algunenano

Algunenano/Parallel plan Secret

Last active March 11, 2019 15:12
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Algunenano/d8767f178f7bc3826f1fc8ddfd291e29 to your computer and use it in GitHub Desktop.
Save Algunenano/d8767f178f7bc3826f1fc8ddfd291e29 to your computer and use it in GitHub Desktop.
Download ZIP
Histogram
Raw
Parallel plan
GroupAggregate (cost=28749.95..28784.46 rows=65 width=104) (actual time=47.890..47.890 rows=1 loops=1)
Group Key: (CASE WHEN (__cdb_basics.__cdb_min_val = __cdb_basics.__cdb_max_val) THEN 0 ELSE (GREATEST(1, LEAST(__cdb_basics.__cdb_bins_number, width_bucket((benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293.st_assem)::double precision, (__cdb_basics.__cdb_min_val)::double precision, (__cdb_basics.__cdb_max_val)::double precision, __cdb_basics.__cdb_bins_number))) - 1) END), __cdb_basics.__cdb_max_val, __cdb_basics.__cdb_min_val, __cdb_basics.__cdb_bins_number
CTE __cdb_basics
-> Subquery Scan on __cdb_basics_2 (cost=14331.07..14331.13 rows=1 width=36) (actual time=29.390..29.391 rows=1 loops=1)
-> Aggregate (cost=14331.07..14331.09 rows=1 width=32) (actual time=29.386..29.386 rows=1 loops=1)
-> Gather (cost=1133.18..14315.19 rows=1270 width=8) (actual time=3.180..28.675 rows=4180 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c7346193986202_1 (cost=133.19..13188.19 rows=529 width=8) (actual time=0.909..4.443 rows=1393 loops=3)
Recheck Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Filter: ((st_assem IS NOT NULL) AND _st_intersects(the_geom_webmercator, '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry))
Rows Removed by Filter: 1
Heap Blocks: exact=3399
-> Bitmap Index Scan on table_2015streettreescensus_trees_the_geom_webmercator_idx (cost=0.00..132.87 rows=3811 width=0) (actual time=1.538..1.538 rows=4184 loops=1)
Index Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
-> Sort (cost=14418.82..14421.99 rows=1270 width=32) (actual time=46.220..46.458 rows=4180 loops=1)
Sort Key: (CASE WHEN (__cdb_basics.__cdb_min_val = __cdb_basics.__cdb_max_val) THEN 0 ELSE (GREATEST(1, LEAST(__cdb_basics.__cdb_bins_number, width_bucket((benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293.st_assem)::double precision, (__cdb_basics.__cdb_min_val)::double precision, (__cdb_basics.__cdb_max_val)::double precision, __cdb_basics.__cdb_bins_number))) - 1) END), __cdb_basics.__cdb_max_val, __cdb_basics.__cdb_min_val, __cdb_basics.__cdb_bins_number
Sort Method: quicksort Memory: 519kB
-> Nested Loop (cost=1133.18..14353.34 rows=1270 width=32) (actual time=32.228..45.006 rows=4180 loops=1)
-> Nested Loop (cost=0.00..0.05 rows=1 width=20) (actual time=29.400..29.406 rows=1 loops=1)
-> CTE Scan on __cdb_basics __cdb_basics_1 (cost=0.00..0.02 rows=1 width=0) (actual time=29.394..29.395 rows=1 loops=1)
-> CTE Scan on __cdb_basics (cost=0.00..0.02 rows=1 width=20) (actual time=0.001..0.005 rows=1 loops=1)
-> Gather (cost=1133.18..14315.19 rows=1270 width=8) (actual time=2.819..14.402 rows=4180 loops=1)
Workers Planned: 2
Workers Launched: 0
-> Parallel Bitmap Heap Scan on benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 (cost=133.19..13188.19 rows=529 width=8) (actual time=2.293..13.187 rows=4180 loops=1)
Recheck Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Filter: _st_intersects(the_geom_webmercator, '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Rows Removed by Filter: 4
Heap Blocks: exact=3399
-> Bitmap Index Scan on table_2015streettreescensus_trees_the_geom_webmercator_idx (cost=0.00..132.87 rows=3811 width=0) (actual time=1.602..1.602 rows=4184 loops=1)
Index Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Planning Time: 1.442 ms
Execution Time: 48.591 ms
Raw
Query
WITH
__cdb_basics AS (
SELECT
*,
CASE
WHEN __cdb_total_rows = 0 OR __cdb_iqr = 0 THEN 1
ELSE GREATEST(
LEAST(
6,
__cdb_total_rows::int),
LEAST(
48,
((__cdb_max_val - __cdb_min_val) / (2 * __cdb_iqr * power(__cdb_total_rows, 1/3)))::int)
)
END AS __cdb_bins_number
FROM
(
SELECT
max(st_assem) AS __cdb_max_val,
min(st_assem) AS __cdb_min_val,
count(1) AS __cdb_total_rows,
percentile_disc(0.75) within group (order by st_assem)
- percentile_disc(0.25) within group (order by st_assem) AS __cdb_iqr
FROM
(
SELECT *
FROM (SELECT * FROM (select * from benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293) _cdb_bbox_filter
WHERE ST_Intersects(the_geom_webmercator, ST_Transform(ST_MakeEnvelope(-74.1796875,40.713955826286046,-74.00390625,40.84706035607122, 4326), 3857))) __cdb_filtered_source_query
WHERE st_assem IS NOT NULL
) __cdb_filtered_source
) __cdb_basics_2
)
SELECT
(__cdb_basics.__cdb_max_val - __cdb_basics.__cdb_min_val) / __cdb_basics.__cdb_bins_number::float AS bin_width,
__cdb_basics.__cdb_bins_number as bins_number,
sum(CASE WHEN (st_assem IS NULL) THEN 1 ELSE 0 END) AS nulls_count,
0 AS infinities_count,
0 AS nans_count,
min(st_assem) AS min,
max(st_assem) AS max,
avg(st_assem) AS avg,
sum(CASE WHEN (st_assem is not NULL) THEN 1 ELSE 0 END) as freq,
CASE WHEN __cdb_basics.__cdb_min_val = __cdb_basics.__cdb_max_val
THEN 0
ELSE GREATEST(1, LEAST(
__cdb_basics.__cdb_bins_number,
WIDTH_BUCKET(st_assem, __cdb_basics.__cdb_min_val, __cdb_basics.__cdb_max_val, __cdb_basics.__cdb_bins_number))) - 1
END AS bin
FROM
(
SELECT * FROM (SELECT * FROM (select * from benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293) _cdb_bbox_filter
WHERE ST_Intersects(the_geom_webmercator, ST_Transform(ST_MakeEnvelope(-74.1796875,40.713955826286046,-74.00390625,40.84706035607122, 4326), 3857))) __ctx_query, __cdb_basics
) __cdb_filtered_source_query, __cdb_basics
GROUP BY 10, __cdb_basics.__cdb_max_val, __cdb_basics.__cdb_min_val, __cdb_basics.__cdb_bins_number
ORDER BY 10;
Raw
Sequential plan
GroupAggregate (cost=32242.62..32277.13 rows=65 width=104) (actual time=27.443..27.443 rows=1 loops=1)
Group Key: (CASE WHEN (__cdb_basics.__cdb_min_val = __cdb_basics.__cdb_max_val) THEN 0 ELSE (GREATEST(1, LEAST(__cdb_basics.__cdb_bins_number,width_bucket((benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293.st_assem)::double precision, (__cdb_basics.__cdb_min_val)::double precision, (__cdb_basics.__cdb_max_val)::double precision, __cdb_basics.__cdb_bins_number))) - 1) END), __cdb_basics.__cdb_max_val, __cdb_basics.__cdb_min_val, __cdb_basics.__cdb_bins_number
CTE __cdb_basics
-> Subquery Scan on __cdb_basics_2 (cost=16077.41..16077.47 rows=1 width=36) (actual time=12.149..12.150 rows=1 loops=1)
-> Aggregate (cost=16077.41..16077.42 rows=1 width=32) (actual time=12.145..12.146 rows=1 loops=1)
-> Bitmap Heap Scan on benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c7346193986202_1 (cost=133.19..16061.53 rows=1270 width=8) (actual time=1.406..11.234 rows=4180 loops=1)
Recheck Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Filter: ((st_assem IS NOT NULL) AND _st_intersects(the_geom_webmercator, '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry))
Rows Removed by Filter: 4
Heap Blocks: exact=3399
-> Bitmap Index Scan on table_2015streettreescensus_trees_the_geom_webmercator_idx (cost=0.00..132.87 rows=3811 width=0) (actual time=0.944..0.944 rows=4184 loops=1)
Index Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
-> Sort (cost=16165.15..16168.33 rows=1270 width=32) (actual time=25.866..26.097 rows=4180 loops=1)
Sort Key: (CASE WHEN (__cdb_basics.__cdb_min_val = __cdb_basics.__cdb_max_val) THEN 0 ELSE (GREATEST(1, LEAST(__cdb_basics.__cdb_bins_number, width_bucket((benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293.st_assem)::double precision, (__cdb_basics.__cdb_min_val)::double precision, (__cdb_basics.__cdb_max_val)::double precision, __cdb_basics.__cdb_bins_number))) - 1) END), __cdb_basics.__cdb_max_val, __cdb_basics.__cdb_min_val, __cdb_basics.__cdb_bins_number
Sort Method: quicksort Memory: 519kB
-> Nested Loop (cost=133.19..16099.68 rows=1270 width=32) (actual time=13.427..24.621 rows=4180 loops=1)
-> Nested Loop (cost=0.00..0.05 rows=1 width=20) (actual time=12.160..12.166 rows=1 loops=1)
-> CTE Scan on __cdb_basics __cdb_basics_1 (cost=0.00..0.02 rows=1 width=0) (actual time=12.153..12.154 rows=1 loops=1)
-> CTE Scan on __cdb_basics (cost=0.00..0.02 rows=1 width=20) (actual time=0.001..0.005 rows=1 loops=1)
-> Bitmap Heap Scan on benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 (cost=133.19..16061.53 rows=1270 width=8) (actual time=1.247..11.108 rows=4180 loops=1)
Recheck Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Filter: _st_intersects(the_geom_webmercator, '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Rows Removed by Filter: 4
Heap Blocks: exact=3399
-> Bitmap Index Scan on table_2015streettreescensus_trees_the_geom_webmercator_idx (cost=0.00..132.87 rows=3811 width=0) (actual time=0.799..0.799 rows=4184 loops=1)
Index Cond: (the_geom_webmercator && '0103000020110F0000010000000500000052838A421B805FC17218F154C0F5524152838A421B805FC18194364DDC0853414107454AFF6C5FC18194364DDC0853414107454AFF6C5FC17218F154C0F5524152838A421B805FC17218F154C0F55241'::geometry)
Planning Time: 1.442 ms
Execution Time: 27.573 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment