Skip to content

Instantly share code, notes, and snippets.

@xenatisch

xenatisch/query-plan.txt

Created November 29, 2021 10:28
Show Gist options
  • Save xenatisch/85f9f5ff9f10094daac395780b6917a8 to your computer and use it in GitHub Desktop.
Save xenatisch/85f9f5ff9f10094daac395780b6917a8 to your computer and use it in GitHub Desktop.
Download ZIP
Open Data API: Complex query sample
Raw
query-plan.txt
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=594) (actual time=14.645..14.648 rows=40 loops=1)
Task Count: 32
Tuple data received from nodes: 2240 bytes
Tasks Shown: One of 32
-> Task
Tuple data received from node: 53 bytes
Node: host=private-w6.[REDACTED] port=5432 dbname=citus
-> Nested Loop (cost=0.71..196.77 rows=1 width=94) (actual time=0.120..2.282 rows=1 loops=1)
Buffers: shared hit=190
-> Nested Loop (cost=0.43..196.31 rows=1 width=162) (actual time=0.090..2.251 rows=1 loops=1)
Buffers: shared hit=187
-> Nested Loop (cost=0.29..196.08 rows=1 width=136) (actual time=0.039..2.194 rows=35 loops=1)
Buffers: shared hit=117
-> Seq Scan on area_reference_102008 ar (cost=0.00..176.29 rows=1 width=32) (actual time=0.024..2.134 rows=1 loops=1)
Filter: ((area_name)::text = 'England'::text)
Rows Removed by Filter: 7622
Buffers: shared hit=81
-> Index Scan using time_series_p2021_11_27_other_partition_id_area_7ad02a31_197587 on time_series_p2021_11_27_other_197587 ts (cost=0.29..19.61 rows=18 width=112) (actual time=0.013..0.051 rows=35 loops=1)
Index Cond: (((partition_id)::text = '2021_11_27|other'::text) AND (area_id = ar.id) AND (date >= '2021-09-20'::date) AND (date <= '2021-09-29'::date))
Buffers: shared hit=36
-> Index Scan using metric_reference_pkey_102010 on metric_reference_102010 mr (cost=0.14..0.20 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=35)
Index Cond: (id = ts.metric_id)
" Filter: ((released IS TRUE) AND ((metric)::text = ANY ('{newCasesByPublishDate,cumCasesByPublishDate,newDeaths28DaysByPublishDate,cumDeaths28DaysByPublishDate}'::text[])))"
Rows Removed by Filter: 1
Buffers: shared hit=70
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.42 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (id = ts.release_id)
Filter: (released IS TRUE)
Buffers: shared hit=3
Planning Time: 0.821 ms
Execution Time: 2.340 ms
Planning:
Buffers: shared hit=26
Planning Time: 2.761 ms
Execution Time: 16.193 ms
Raw
query.sql
SELECT ts.date,
ar.area_name,
ar.area_code,
mr.metric,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
WHEN (payload ->> 'value') ISNULL
THEN NULL
WHEN metric ILIKE ANY('{%percentage%,%rate%,%transmission%}'::VARCHAR[])
THEN round((payload ->> 'value')::NUMERIC, 1)
ELSE round((payload ->> 'value')::NUMERIC)::INT
END
) AS value
FROM covid19.time_series AS ts
JOIN covid19.metric_reference AS mr ON mr.id = ts.metric_id
JOIN covid19.area_reference AS ar ON ar.id = ts.area_id
JOIN covid19.release_reference AS rr ON rr.id = ts.release_id
WHERE ts.partition_id = '2021_11_27|other'
AND ar.area_name = 'England'
AND mr.metric = ANY('{newCasesByPublishDate,cumCasesByPublishDate,newDeaths28DaysByPublishDate,cumDeaths28DaysByPublishDate}'::VARCHAR[])
AND ts.date BETWEEN '2021-09-20' AND '2021-09-29'
AND rr.released IS TRUE
AND mr.released IS TRUE;
Raw
raw_results.md
date area_name area_code metric value
2021-09-20 England E92000001 cumDeaths28DaysByPublishDate 118551
2021-09-27 England E92000001 newCasesByPublishDate 29329
2021-09-27 England E92000001 cumDeaths28DaysByPublishDate 119261
2021-09-28 England E92000001 cumDeaths28DaysByPublishDate 119402
2021-09-24 England E92000001 cumCasesByPublishDate 6481363
2021-09-20 England E92000001 newDeaths28DaysByPublishDate 27
2021-09-21 England E92000001 newDeaths28DaysByPublishDate 174
2021-09-22 England E92000001 newDeaths28DaysByPublishDate 121
2021-09-26 England E92000001 cumDeaths28DaysByPublishDate 119238
2021-09-23 England E92000001 cumCasesByPublishDate 6453164
2021-09-24 England E92000001 newDeaths28DaysByPublishDate 120
2021-09-25 England E92000001 newCasesByPublishDate 28087
2021-09-21 England E92000001 cumCasesByPublishDate 6398633
2021-09-22 England E92000001 cumCasesByPublishDate 6425056
2021-09-23 England E92000001 newDeaths28DaysByPublishDate 131
2021-09-21 England E92000001 newCasesByPublishDate 24973
2021-09-27 England E92000001 newDeaths28DaysByPublishDate 23
2021-09-26 England E92000001 newDeaths28DaysByPublishDate 37
2021-09-20 England E92000001 cumCasesByPublishDate 6374123
2021-09-25 England E92000001 cumCasesByPublishDate 6507849
2021-09-21 England E92000001 cumDeaths28DaysByPublishDate 118725
2021-09-29 England E92000001 cumDeaths28DaysByPublishDate 119513
2021-09-24 England E92000001 cumDeaths28DaysByPublishDate 119097
2021-09-24 England E92000001 newCasesByPublishDate 28199
2021-09-25 England E92000001 newDeaths28DaysByPublishDate 104
2021-09-25 England E92000001 cumDeaths28DaysByPublishDate 119201
2021-09-26 England E92000001 newCasesByPublishDate 25538
2021-09-27 England E92000001 cumCasesByPublishDate 6561706
2021-09-29 England E92000001 cumCasesByPublishDate 6617892
2021-09-20 England E92000001 newCasesByPublishDate 26905
2021-09-23 England E92000001 cumDeaths28DaysByPublishDate 118977
2021-09-28 England E92000001 cumCasesByPublishDate 6590514
2021-09-26 England E92000001 cumCasesByPublishDate 6532847
2021-09-22 England E92000001 cumDeaths28DaysByPublishDate 118846
2021-09-28 England E92000001 newDeaths28DaysByPublishDate 141
2021-09-29 England E92000001 newCasesByPublishDate 29036
2021-09-28 England E92000001 newCasesByPublishDate 28808
2021-09-22 England E92000001 newCasesByPublishDate 27317
2021-09-23 England E92000001 newCasesByPublishDate 29045
2021-09-29 England E92000001 newDeaths28DaysByPublishDate 111
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment