Skip to content

Instantly share code, notes, and snippets.

@xenatisch

xenatisch/cache_repopulation-normal.sql

Created November 21, 2021 12:02
Show Gist options
  • Save xenatisch/75012db7f9ccd1ea2470e55bca019eb9 to your computer and use it in GitHub Desktop.
Save xenatisch/75012db7f9ccd1ea2470e55bca019eb9 to your computer and use it in GitHub Desktop.
Download ZIP
Normal and optimised versions of Postgres (Citus) queries and plans thereof
Raw
cache_repopulation-normal.sql
SELECT
'area-' || release_date::TEXT || '-' || area_id::TEXT AS key,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'area_code', area_code,
'area_type', area_type,
'area_name', area_name,
'date', to_char(date::DATE, 'YYYY-MM-DD'),
'metric', metric,
'value', value,
'priority', priority
)
)::TEXT AS value
FROM (
-- Data at UK / NATION / REGION / NHS REGION levels.
SELECT
CASE
WHEN ref.area_type = 'overview' THEN 'UK'
ELSE ts.area_id::TEXT
END AS area_id,
rr.timestamp::DATE AS release_date,
metric,
priority,
area_code,
ref.area_type,
area_name,
ts.date AS date,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
WHEN metric ILIKE ANY('{%percentage%,%rate%,%transmission%}'::VARCHAR[])
THEN round((payload ->> 'value')::NUMERIC, 1)
ELSE round((payload ->> 'value')::NUMERIC)::INT
END
) AS value,
RANK() OVER ( PARTITION BY (ts.area_id, metric) ORDER BY priority, date DESC ) AS rank
FROM covid19.time_series_p2021_11_18_other AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE ts.date > (NOW() - INTERVAL '18 days')
AND (
(
-- UK level data.
ref.area_type = 'overview'
AND metric = ANY('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection,transmissionRateMin,transmissionRateMax,newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate, newDeaths28DaysByPublishDateChange, newDeaths28DaysByPublishDateChangePercentage, newDeaths28DaysByPublishDateRollingSum, newDeaths28DaysByPublishDateDirection, newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingRate, newCasesByPublishDate, newCasesByPublishDateChange, newCasesByPublishDateChangePercentage, newCasesByPublishDateRollingSum, newCasesByPublishDateDirection, newPeopleVaccinatedThirdInjectionByPublishDate, cumPeopleVaccinatedThirdInjectionByPublishDate, cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::VARCHAR[])
)
OR (
-- Anything else that's available at nation, region, or NHS region levels.
metric = ANY('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,transmissionRateMin,transmissionRateMax}'::VARCHAR[])
OR (
-- National testing data.
ref.area_type = 'nation'
AND metric = ANY ('{newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection}'::VARCHAR[])
)
OR (
LEFT(ref.area_code, 1) = 'W' -- Welsh deaths only available at nation level.
AND metric = ANY ('{newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate}'::VARCHAR[])
)
OR (
-- Welsh & NI vax only available at nation level + England & Scotland for fall back.
LEFT(ref.area_code, 1) = ANY('{W,N,E,S}'::VARCHAR[])
AND metric = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate, newDeaths28DaysByPublishDateChange, newDeaths28DaysByPublishDateChangePercentage, newDeaths28DaysByPublishDateRollingSum, newDeaths28DaysByPublishDateDirection, newDeaths28DaysByDeathDateRollingRate, newPeopleVaccinatedThirdInjectionByPublishDate, cumPeopleVaccinatedThirdInjectionByPublishDate, cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::VARCHAR[])
)
)
)
UNION
(
-- Data at UTLA / LTLA REGION levels.
SELECT area_id,
release_date,
metric,
priority,
area_code,
area_type,
area_name,
date,
value,
RANK() OVER ( PARTITION BY (metric, priority) ORDER BY date DESC ) AS rank
FROM (
-- Data at UTLA level
SELECT ts.area_id::TEXT,
rr.timestamp::DATE AS release_date,
metric,
priority,
area_code,
ref.area_type,
area_name,
date,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
WHEN metric ILIKE ANY('{%percentage%,%rate%,%transmission%}'::VARCHAR[])
THEN round((payload ->> 'value')::NUMERIC, 2)
ELSE round((payload ->> 'value')::NUMERIC)::INT
END
) AS value
FROM covid19.time_series_p2021_11_18_utla AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE ts.date > (NOW() - INTERVAL '10 days')
AND metric = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingSum,newCasesBySpecimenDateRollingRate,newCasesBySpecimenDateChange,newCasesBySpecimenDateChangePercentage,newCasesBySpecimenDateDirection,newCasesBySpecimenDate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate, cumPeopleVaccinatedThirdInjectionByPublishDate, cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::VARCHAR[])
UNION
(
-- Data at LTLA level.
SELECT ts.area_id::TEXT,
rr.timestamp::DATE AS release_date,
metric,
priority,
area_code,
ref.area_type,
area_name,
date,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
WHEN metric ILIKE ANY('{%percentage%,%rate%,%transmission%}'::VARCHAR[])
THEN round((payload ->> 'value')::NUMERIC, 2)
ELSE round((payload ->> 'value')::NUMERIC)::INT
END
) AS value
FROM covid19.time_series_p2021_11_18_ltla AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE ts.date > (NOW() - INTERVAL '10 days')
AND metric = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingSum,newCasesBySpecimenDateRollingRate,newCasesBySpecimenDateChange,newCasesBySpecimenDateChangePercentage,newCasesBySpecimenDateDirection,newCasesBySpecimenDate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate, cumPeopleVaccinatedThirdInjectionByPublishDate, cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::VARCHAR[])
)
) AS ts2
)
UNION
(
-- Data at NHS TRUST level.
SELECT ts.area_id::TEXT,
rr.timestamp::DATE AS release_date,
metric,
priority,
area_code,
ref.area_type,
area_name,
date,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
WHEN metric ILIKE ANY('{%percentage%,%rate%,%transmission%}'::VARCHAR[])
THEN round((payload ->> 'value')::NUMERIC, 2)
ELSE round((payload ->> 'value')::NUMERIC)::INT
END
) AS value,
RANK() OVER ( PARTITION BY (metric) ORDER BY priority, date DESC ) AS rank
FROM covid19.time_series_p2021_11_18_nhstrust AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE ts.date > (NOW() - INTERVAL '16 days')
AND metric = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection}'::VARCHAR[])
AND (payload ->> 'value') NOTNULL
)
UNION
(
-- Cases data at MSOA level.
SELECT ts.area_id::TEXT,
release_date,
metric,
1 AS priority,
area_code,
area_type,
area_name,
date,
(
CASE
WHEN value::TEXT = 'UP'
THEN 0
WHEN value::TEXT = 'DOWN'
THEN 180
WHEN value::TEXT = 'SAME'
THEN 90
WHEN metric LIKE 'newCasesBySpecimenDate%'
THEN value::NUMERIC
ELSE round(value::NUMERIC)::INT
END
) AS value,
rank
FROM (
SELECT area_id,
'msoa_' || metric || UPPER(LEFT(key, 1)) || RIGHT(key, -1) AS metric,
rr.timestamp::DATE AS release_date,
ref.area_code,
ref.area_type ,
area_name,
date,
(
CASE
WHEN value::TEXT <> 'null'
THEN TRIM(BOTH '\"' FROM value::TEXT)
ELSE '-999999'
END
) AS value,
RANK() OVER ( PARTITION BY (metric) ORDER BY date DESC ) AS rank
FROM covid19.time_series_p2021_11_18_msoa AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id,
jsonb_each(payload) AS pa
WHERE metric = 'newCasesBySpecimenDate'
AND ts.date > (NOW() - INTERVAL '10 days')
) AS ts
)
UNION
(
-- Vaccinations data at MSOA level.
SELECT ts.area_id::TEXT,
rr.timestamp::DATE AS release_date,
'msoa_' || mr.metric,
1 AS priority,
area_code,
ref.area_type,
area_name,
date,
ROUND((payload ->> 'value')::NUMERIC, 2) AS value,
RANK() OVER ( PARTITION BY (metric) ORDER BY date DESC ) AS rank
FROM covid19.time_series_p2021_11_18_msoa AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
WHERE ts.date > (NOW() - INTERVAL '10 days')
AND mr.metric = ANY('{cumVaccinationFirstDoseUptakeByVaccinationDatePercentage,cumVaccinationSecondDoseUptakeByVaccinationDatePercentage}'::VARCHAR[])
)
) AS ts
WHERE rank = 1
GROUP BY release_date, area_id;
Raw
cache_repopulation-optimised.sql
SELECT
'area-' || release_date::TEXT || '-' || area_id::TEXT AS key,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'area_code', area_code,
'area_type', area_type,
'area_name', area_name,
'date', to_char(date::DATE, 'YYYY-MM-DD'),
'metric', metric,
'value', data_outer.value,
'priority', priority
)
)::TEXT AS value
FROM (
SELECT
area_id::TEXT,
release_date,
metric,
priority,
area_code,
area_type,
area_name,
date,
data_inner.value,
RANK() OVER ( PARTITION BY (metric, priority) ORDER BY date DESC ) AS rank
FROM (
-- Data at UK / NATION / REGION / NHS REGION levels.
SELECT
hash,
CASE
WHEN ref.area_type = 'overview'
THEN 'UK'
ELSE ts.area_id::TEXT
END AS area_id,
rr.timestamp::DATE AS release_date,
metric,
priority,
area_code,
ref.area_type,
area_name,
ts.date AS date,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
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.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE partition_id = '2021_11_18|other'
AND ts.date > (NOW() - INTERVAL '18 days')
AND (
(
-- UK level data.
ref.area_type = 'overview'
AND metric = ANY('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection,transmissionRateMin,transmissionRateMax,newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate, newDeaths28DaysByPublishDateChange, newDeaths28DaysByPublishDateChangePercentage, newDeaths28DaysByPublishDateRollingSum, newDeaths28DaysByPublishDateDirection, newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingRate, newCasesByPublishDate, newCasesByPublishDateChange, newCasesByPublishDateChangePercentage, newCasesByPublishDateRollingSum, newCasesByPublishDateDirection, newPeopleVaccinatedThirdInjectionByPublishDate, cumPeopleVaccinatedThirdInjectionByPublishDate, cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::VARCHAR[])
)
OR (
-- Anything else that's available at nation, region, or NHS region levels.
metric = ANY('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,transmissionRateMin,transmissionRateMax}'::VARCHAR[])
OR (
-- National testing data.
ref.area_type = 'nation'
AND metric = ANY ('{newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection}'::VARCHAR[])
)
OR (
LEFT(ref.area_code, 1) = 'W' -- Welsh deaths only available at nation level.
AND metric = ANY ('{newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate}'::VARCHAR[])
)
OR (
-- Welsh & NI vax only available at nation level + England & Scotland for fall back.
LEFT(ref.area_code, 1) = ANY('{W,N,E,S}'::VARCHAR[])
AND metric = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate, newDeaths28DaysByPublishDateChange, newDeaths28DaysByPublishDateChangePercentage, newDeaths28DaysByPublishDateRollingSum, newDeaths28DaysByPublishDateDirection, newDeaths28DaysByDeathDateRollingRate, newPeopleVaccinatedThirdInjectionByPublishDate, cumPeopleVaccinatedThirdInjectionByPublishDate, cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::VARCHAR[])
)
)
)
UNION
(
-- Data at UTLA / LTLA level
SELECT hash,
ts.area_id::TEXT,
rr.timestamp::DATE AS release_date,
metric,
priority,
area_code,
ref.area_type,
area_name,
date,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
WHEN metric ILIKE ANY('{%percentage%,%rate%,%transmission%}'::VARCHAR[])
THEN round((payload ->> 'value')::NUMERIC, 2)
ELSE round((payload ->> 'value')::NUMERIC)::INT
END
) AS value
FROM covid19.time_series AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE partition_id = ANY('{2021_11_18|utla,2021_11_18|ltla}'::TEXT[])
AND ts.date > (NOW() - INTERVAL '10 days')
AND metric = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingSum,newCasesBySpecimenDateRollingRate,newCasesBySpecimenDateChange,newCasesBySpecimenDateChangePercentage,newCasesBySpecimenDateDirection,newCasesBySpecimenDate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate, cumPeopleVaccinatedThirdInjectionByPublishDate, cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::VARCHAR[])
)
UNION
(
-- Data at NHS TRUST level.
SELECT hash,
ts.area_id::TEXT,
rr.timestamp::DATE AS release_date,
metric,
priority,
area_code,
ref.area_type,
area_name,
date,
(
CASE
WHEN (payload ->> 'value') = 'UP'
THEN 0
WHEN (payload ->> 'value') = 'DOWN'
THEN 180
WHEN (payload ->> 'value') = 'SAME'
THEN 90
WHEN metric ILIKE ANY('{%percentage%,%rate%,%transmission%}'::VARCHAR[])
THEN round((payload ->> 'value')::NUMERIC, 2)
ELSE round((payload ->> 'value')::NUMERIC)::INT
END
) AS value
FROM covid19.time_series AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE partition_id = '2021_11_18|nhstrust'
AND ts.date > (NOW() - INTERVAL '16 days')
AND metric = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection}'::VARCHAR[])
AND (payload ->> 'value') NOTNULL
)
UNION
(
-- Vaccinations data at MSOA level.
SELECT hash,
ts.area_id::TEXT,
rr.timestamp::DATE AS release_date,
'msoa_' || mr.metric,
1 AS priority,
area_code,
ref.area_type,
area_name,
date,
ROUND((payload ->> 'value')::NUMERIC, 2) AS value
FROM covid19.time_series AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id
WHERE partition_id = '2021_11_18|msoa'
AND ts.date > (NOW() - INTERVAL '10 days')
AND mr.metric = ANY('{cumVaccinationFirstDoseUptakeByVaccinationDatePercentage,cumVaccinationSecondDoseUptakeByVaccinationDatePercentage}'::VARCHAR[])
)
UNION
(
-- Cases data at MSOA level.
SELECT hash,
area_id::TEXT,
release_date,
metric,
1 AS priority,
area_code,
area_type,
area_name,
date,
(
CASE
WHEN value::TEXT = 'UP'
THEN 0
WHEN value::TEXT = 'DOWN'
THEN 180
WHEN value::TEXT = 'SAME'
THEN 90
WHEN metric LIKE '%newCasesBySpecimenDate%'
THEN value::NUMERIC
ELSE round(value::NUMERIC)::INT
END
) AS value
FROM (
SELECT hash,
area_id::TEXT,
'msoa_' || (metric || UPPER(LEFT(key, 1)) || RIGHT(key, -1)) AS metric,
rr.timestamp::DATE AS release_date,
ref.area_code,
ref.area_type ,
area_name,
date,
(
CASE
WHEN value::TEXT <> 'null' THEN TRIM(BOTH '\"' FROM value::TEXT)
ELSE '-999999'
END
) AS value
FROM covid19.time_series AS ts
JOIN covid19.release_reference AS rr ON rr.id = release_id
JOIN covid19.metric_reference AS mr ON mr.id = metric_id
JOIN covid19.area_reference AS ref ON ref.id = area_id,
jsonb_each(payload) AS pa
WHERE partition_id = 'p2021_11_18|msoa'
AND metric = 'newCasesBySpecimenDate'
AND ts.date > (NOW() - INTERVAL '10 days')
) AS ttm
)
) AS data_inner
) AS data_outer
WHERE data_outer.rank = 1
GROUP BY release_date, area_id;
Raw
query_plan-normal.txt
QUERY PLAN
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=913.418..914.407 rows=7608 loops=1)
-> Distributed Subplan 45_1
Subplan Duration: 41.73 ms
Intermediate Data Size: 417 kB
Result destination: Send to 1 nodes
-> WindowAgg (cost=8304.82..10804.82 rows=100000 width=722) (actual time=42.994..45.232 rows=3029 loops=1)
-> Sort (cost=8304.82..8554.82 rows=100000 width=714) (actual time=42.981..43.133 rows=3029 loops=1)
Sort Key: remote_scan.worker_column_10, remote_scan.metric, remote_scan.priority, remote_scan.date DESC
Sort Method: quicksort Memory: 537kB
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=714) (actual time=33.979..34.509 rows=3029 loops=1)
Task Count: 32
Tuple data received from nodes: 281 kB
Tasks Shown: One of 32
-> Task
Tuple data received from node: 8258 bytes
Node: host=private-w7.[REDACTED] port=5432 dbname=citus
-> Nested Loop (cost=21.79..738.86 rows=14 width=144) (actual time=0.355..2.989 rows=86 loops=1)
Buffers: shared hit=678
-> Nested Loop (cost=21.65..736.53 rows=14 width=179) (actual time=0.329..2.548 rows=86 loops=1)
Buffers: shared hit=670
-> Hash Join (cost=21.36..732.89 rows=14 width=175) (actual time=0.322..2.449 rows=86 loops=1)
Hash Cond: (ts.metric_id = mr.id)
" Join Filter: ((((ref.area_type)::text = 'overview'::text) AND ((mr.metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection,transmissionRateMin,transmissionRateMax,newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingRate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[]))) OR ((mr.metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,transmissionRateMin,transmissionRateMax}'::text[])) OR (((ref.area_type)::text = 'nation'::text) AND ((mr.metric)::text = ANY ('{newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection}'::text[]))) OR ((""left""((ref.area_code)::text, 1) = 'W'::text) AND ((mr.metric)::text = ANY ('{newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate}'::text[]))) OR ((""left""((ref.area_code)::text, 1) = ANY ('{W,N,E,S}'::text[])) AND ((mr.metric)::text = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[]))))"
Rows Removed by Join Filter: 45
Buffers: shared hit=658
-> Nested Loop (cost=0.58..710.79 rows=504 width=149) (actual time=0.096..2.067 rows=513 loops=1)
Buffers: shared hit=653
-> Index Scan using time_series_p2021_11_18_other_partition_id_area_783b137a_196142 on time_series_p2021_11_18_other_196142 ts (cost=0.29..682.55 rows=504 width=116) (actual time=0.085..1.631 rows=513 loops=1)
Index Cond: (date > (now() - '18 days'::interval))
Buffers: shared hit=590
-> Memoize (cost=0.29..0.72 rows=1 width=37) (actual time=0.000..0.000 rows=1 loops=513)
Cache Key: ts.area_id
Hits: 492 Misses: 21 Evictions: 0 Overflows: 0 Memory Usage: 3kB
Buffers: shared hit=63
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref (cost=0.28..0.71 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=21)
Index Cond: (id = ts.area_id)
Buffers: shared hit=63
-> Hash (cost=19.93..19.93 rows=61 width=34) (actual time=0.216..0.216 rows=33 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Buffers: shared hit=5
-> Seq Scan on metric_reference_102010 mr (cost=0.08..19.93 rows=61 width=34) (actual time=0.042..0.208 rows=33 loops=1)
Filter: (((metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection,transmissionRateMin,transmissionRateMax,newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingRate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[])) OR ((metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,transmissionRateMin,transmissionRateMax}'::text[])) OR ((metric)::text = ANY ('{newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection}'::text[])) OR ((metric)::text = ANY ('{newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate}'::text[])) OR ((metric)::text = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[])))
Rows Removed by Filter: 182
Buffers: shared hit=5
-> Memoize (cost=0.29..0.40 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=86)
Cache Key: ts.release_id
Hits: 82 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=12
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.39 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=4)
Index Cond: (id = ts.release_id)
Buffers: shared hit=12
-> Memoize (cost=0.14..0.16 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=86)
Cache Key: ref.area_type
Hits: 82 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=8
-> Index Only Scan using area_priorities_pkey_126139 on area_priorities_126139 ap (cost=0.13..0.15 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=4)
Index Cond: (area_type = (ref.area_type)::text)
Heap Fetches: 4
Buffers: shared hit=8
Planning Time: 2.618 ms
Execution Time: 3.123 ms
Planning Time: 0.000 ms
Execution Time: 45.511 ms
-> Distributed Subplan 45_2
Subplan Duration: 134.10 ms
Intermediate Data Size: 2364 kB
Result destination: Send to 1 nodes
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=710) (actual time=86.186..87.443 rows=19104 loops=1)
Task Count: 32
Tuple data received from nodes: 1655 kB
Tasks Shown: One of 32
-> Task
Tuple data received from node: 53 kB
Node: host=private-w10.[REDACTED] port=5432 dbname=citus
-> Nested Loop (cost=4843.48..4918.03 rows=267 width=140) (actual time=15.554..18.696 rows=615 loops=1)
Buffers: shared hit=2913
-> Merge Join (cost=4843.19..4895.47 rows=267 width=124) (actual time=15.515..16.398 rows=615 loops=1)
Merge Cond: (ref.id = ts.area_id)
Buffers: shared hit=2907
-> Nested Loop (cost=0.42..426.93 rows=7623 width=42) (actual time=0.016..0.624 rows=824 loops=1)
Buffers: shared hit=65
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref (cost=0.28..246.13 rows=7623 width=37) (actual time=0.006..0.176 rows=824 loops=1)
Buffers: shared hit=51
-> Memoize (cost=0.14..0.16 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=824)
Cache Key: ref.area_type
Hits: 817 Misses: 7 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=14
-> Index Only Scan using area_priorities_pkey_126139 on area_priorities_126139 ap (cost=0.13..0.15 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=7)
Index Cond: (area_type = (ref.area_type)::text)
Heap Fetches: 7
Buffers: shared hit=14
-> Sort (cost=4842.76..4843.43 rows=267 width=86) (actual time=15.492..15.539 rows=615 loops=1)
Sort Key: ts.area_id
Sort Method: quicksort Memory: 111kB
Buffers: shared hit=2842
-> Hash Join (cost=8.98..4832.00 rows=267 width=86) (actual time=0.164..15.269 rows=615 loops=1)
Hash Cond: (ts.metric_id = mr.id)
Buffers: shared hit=2842
-> Index Scan using time_series_p2021_11_18_utla_partition_id_area__8470f11b_196111 on time_series_p2021_11_18_utla_196111 ts (cost=0.42..4817.66 rows=2164 width=60) (actual time=0.090..14.763 rows=2047 loops=1)
Index Cond: (date > (now() - '10 days'::interval))
Buffers: shared hit=2837
-> Hash (cost=8.23..8.23 rows=26 width=34) (actual time=0.066..0.067 rows=26 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
Buffers: shared hit=5
-> Seq Scan on metric_reference_102010 mr (cost=0.07..8.23 rows=26 width=34) (actual time=0.015..0.059 rows=26 loops=1)
Filter: ((metric)::text = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingSum,newCasesBySpecimenDateRollingRate,newCasesBySpecimenDateChange,newCasesBySpecimenDateChangePercentage,newCasesBySpecimenDateDirection,newCasesBySpecimenDate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[]))
Rows Removed by Filter: 189
Buffers: shared hit=5
-> Memoize (cost=0.29..0.33 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=615)
Cache Key: ts.release_id
Hits: 613 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=6
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.32 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=2)
Index Cond: (id = ts.release_id)
Buffers: shared hit=6
Planning Time: 1.177 ms
Execution Time: 18.956 ms
Planning Time: 0.000 ms
Execution Time: 88.537 ms
-> Distributed Subplan 45_3
Subplan Duration: 232.13 ms
Intermediate Data Size: 4276 kB
Result destination: Send to 1 nodes
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=710) (actual time=153.206..155.430 rows=34708 loops=1)
Task Count: 32
Tuple data received from nodes: 2988 kB
Tasks Shown: One of 32
-> Task
Tuple data received from node: 94 kB
Node: host=private-w10.[REDACTED] port=5432 dbname=citus
-> Nested Loop (cost=9016.94..9111.23 rows=484 width=140) (actual time=32.083..37.223 rows=1087 loops=1)
Buffers: shared hit=5279
-> Merge Join (cost=9016.65..9072.19 rows=484 width=124) (actual time=32.035..33.224 rows=1087 loops=1)
Merge Cond: (ref.id = ts.area_id)
Buffers: shared hit=5273
-> Nested Loop (cost=0.42..426.93 rows=7623 width=42) (actual time=0.019..0.784 rows=826 loops=1)
Buffers: shared hit=65
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref (cost=0.28..246.13 rows=7623 width=37) (actual time=0.008..0.259 rows=826 loops=1)
Buffers: shared hit=51
-> Memoize (cost=0.14..0.16 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=826)
Cache Key: ref.area_type
Hits: 819 Misses: 7 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=14
-> Index Only Scan using area_priorities_pkey_126139 on area_priorities_126139 ap (cost=0.13..0.15 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=7)
Index Cond: (area_type = (ref.area_type)::text)
Heap Fetches: 7
Buffers: shared hit=14
-> Sort (cost=9016.17..9017.38 rows=484 width=86) (actual time=31.978..32.068 rows=1087 loops=1)
Sort Key: ts.area_id
Sort Method: quicksort Memory: 201kB
Buffers: shared hit=5208
-> Hash Join (cost=8.98..8994.58 rows=484 width=86) (actual time=0.142..31.659 rows=1087 loops=1)
Hash Cond: (ts.metric_id = mr.id)
Buffers: shared hit=5208
-> Index Scan using time_series_p2021_11_18_ltla_partition_id_area__ee4e6e61_196091 on time_series_p2021_11_18_ltla_196091 ts (cost=0.43..8975.51 rows=3927 width=60) (actual time=0.060..30.768 rows=3745 loops=1)
Index Cond: (date > (now() - '10 days'::interval))
Buffers: shared hit=5203
-> Hash (cost=8.23..8.23 rows=26 width=34) (actual time=0.075..0.076 rows=26 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
Buffers: shared hit=5
-> Seq Scan on metric_reference_102010 mr (cost=0.07..8.23 rows=26 width=34) (actual time=0.018..0.068 rows=26 loops=1)
Filter: ((metric)::text = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingSum,newCasesBySpecimenDateRollingRate,newCasesBySpecimenDateChange,newCasesBySpecimenDateChangePercentage,newCasesBySpecimenDateDirection,newCasesBySpecimenDate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[]))
Rows Removed by Filter: 189
Buffers: shared hit=5
-> Memoize (cost=0.29..0.32 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=1087)
Cache Key: ts.release_id
Hits: 1085 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=6
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.31 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=2)
Index Cond: (id = ts.release_id)
Buffers: shared hit=6
Planning Time: 1.310 ms
Execution Time: 37.662 ms
Planning Time: 0.000 ms
Execution Time: 157.435 ms
-> Distributed Subplan 45_4
Subplan Duration: 375.89 ms
Intermediate Data Size: 6640 kB
Result destination: Send to 1 nodes
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=260.148..263.293 rows=53812 loops=1)
Task Count: 1
Tuple data received from nodes: 4643 kB
Tasks Shown: All
-> Task
Tuple data received from node: 4643 kB
Node: host=private-w6.[REDACTED] port=5432 dbname=citus
-> HashAggregate (cost=1375.40..1466.30 rows=9090 width=710) (actual time=104.152..123.103 rows=53812 loops=1)
Group Key: intermediate_result.area_id, intermediate_result.release_date, intermediate_result.metric, intermediate_result.priority, intermediate_result.area_code, intermediate_result.area_type, intermediate_result.area_name, intermediate_result.date, intermediate_result.value
Batches: 1 Memory Usage: 11305kB
-> Append (cost=0.00..1170.88 rows=9090 width=710) (actual time=18.391..61.197 rows=53812 loops=1)
-> Function Scan on read_intermediate_result intermediate_result (cost=0.00..368.27 rows=3236 width=710) (actual time=18.390..19.655 rows=19104 loops=1)
-> Function Scan on read_intermediate_result intermediate_result_1 (cost=0.00..666.25 rows=5854 width=710) (actual time=35.148..37.569 rows=34708 loops=1)
Planning Time: 0.117 ms
Execution Time: 139.216 ms
Planning Time: 0.000 ms
Execution Time: 266.470 ms
-> Distributed Subplan 45_5
Subplan Duration: 71.60 ms
Intermediate Data Size: 1368 kB
Result destination: Send to 1 nodes
-> WindowAgg (cost=8304.82..10554.82 rows=100000 width=718) (actual time=57.580..65.577 rows=8946 loops=1)
-> Sort (cost=8304.82..8554.82 rows=100000 width=710) (actual time=57.484..58.009 rows=8946 loops=1)
Sort Key: remote_scan.metric, remote_scan.priority, remote_scan.date DESC
Sort Method: quicksort Memory: 1889kB
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=710) (actual time=45.437..46.642 rows=8946 loops=1)
Task Count: 32
Tuple data received from nodes: 931 kB
Tasks Shown: One of 32
-> Task
Tuple data received from node: 29 kB
Node: host=private-w5.[REDACTED] port=5432 dbname=citus
-> Hash Join (cost=567.56..1074.89 rows=14 width=140) (actual time=6.071..11.599 rows=276 loops=1)
Hash Cond: ((ref.area_type)::text = (ap.area_type)::text)
Buffers: shared hit=1370
-> Nested Loop (cost=566.38..1072.93 rows=14 width=104) (actual time=6.026..9.341 rows=276 loops=1)
Buffers: shared hit=1369
-> Nested Loop (cost=566.09..1063.80 rows=14 width=71) (actual time=6.015..8.185 rows=276 loops=1)
Buffers: shared hit=541
-> Nested Loop (cost=565.80..1062.73 rows=14 width=67) (actual time=6.001..7.845 rows=276 loops=1)
Buffers: shared hit=538
-> Bitmap Heap Scan on time_series_p2021_11_18_nhstrust_196224 ts (cost=565.65..1045.90 rows=590 width=41) (actual time=5.952..7.237 rows=460 loops=1)
Recheck Cond: (date > (now() - '16 days'::interval))
Filter: ((payload ->> 'value'::text) IS NOT NULL)
Rows Removed by Filter: 155
Heap Blocks: exact=385
Buffers: shared hit=522
-> Bitmap Index Scan on time_series_p2021_11_18_nhstrust_area_id_date_idx_196224 (cost=0.00..565.50 rows=593 width=0) (actual time=5.825..5.825 rows=615 loops=1)
Index Cond: (date > (now() - '16 days'::interval))
Buffers: shared hit=137
-> Memoize (cost=0.15..0.20 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=460)
Cache Key: ts.metric_id
Hits: 452 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=16
-> Index Scan using metric_reference_pkey_102010 on metric_reference_102010 mr (cost=0.14..0.19 rows=1 width=34) (actual time=0.005..0.005 rows=1 loops=8)
Index Cond: (id = ts.metric_id)
Filter: ((metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection}'::text[]))
Rows Removed by Filter: 0
Buffers: shared hit=16
-> Memoize (cost=0.29..0.38 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=276)
Cache Key: ts.release_id
Hits: 275 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=3
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.37 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = ts.release_id)
Buffers: shared hit=3
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref (cost=0.28..0.65 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=276)
Index Cond: (id = ts.area_id)
Buffers: shared hit=828
-> Hash (cost=1.08..1.08 rows=8 width=12) (actual time=0.024..0.025 rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on area_priorities_126139 ap (cost=0.00..1.08 rows=8 width=12) (actual time=0.011..0.014 rows=8 loops=1)
Buffers: shared hit=1
Planning Time: 2.153 ms
Execution Time: 11.999 ms
Planning Time: 0.000 ms
Execution Time: 66.134 ms
-> Distributed Subplan 45_6
Subplan Duration: 201.85 ms
Intermediate Data Size: 4336 kB
Result destination: Send to 1 nodes
-> WindowAgg (cost=8304.82..10304.82 rows=100000 width=690) (actual time=111.786..125.355 rows=33955 loops=1)
-> Sort (cost=8304.82..8554.82 rows=100000 width=682) (actual time=99.433..101.145 rows=33955 loops=1)
Sort Key: remote_scan.worker_column_9, remote_scan.date DESC
Sort Method: quicksort Memory: 8714kB
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=682) (actual time=80.549..85.221 rows=33955 loops=1)
Task Count: 32
Tuple data received from nodes: 3540 kB
Tasks Shown: One of 32
-> Task
Tuple data received from node: 108 kB
Node: host=private-w10.[REDACTED] port=5432 dbname=citus
-> Nested Loop (cost=1.01..490.60 rows=100 width=139) (actual time=0.189..9.071 rows=1040 loops=1)
Buffers: shared hit=3097
-> Nested Loop (cost=1.01..485.35 rows=1 width=203) (actual time=0.156..5.662 rows=208 loops=1)
Buffers: shared hit=3097
-> Nested Loop (cost=0.73..484.04 rows=1 width=170) (actual time=0.148..5.075 rows=208 loops=1)
Buffers: shared hit=2470
-> Nested Loop (cost=0.45..483.52 rows=1 width=166) (actual time=0.141..4.568 rows=208 loops=1)
Buffers: shared hit=1846
-> Index Scan Backward using time_series_p2021_11_18_msoa_area_id_date_idx_196175 on time_series_p2021_11_18_msoa_196175 ts (cost=0.29..477.89 rows=207 width=140) (actual time=0.084..3.554 rows=1707 loops=1)
Index Cond: (date > (now() - '10 days'::interval))
Buffers: shared hit=1830
-> Memoize (cost=0.15..0.24 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=1707)
Cache Key: ts.metric_id
Hits: 1699 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=16
-> Index Scan using metric_reference_pkey_102010 on metric_reference_102010 mr (cost=0.14..0.23 rows=1 width=34) (actual time=0.004..0.004 rows=0 loops=8)
Index Cond: (id = ts.metric_id)
Filter: ((metric)::text = 'newCasesBySpecimenDate'::text)
Rows Removed by Filter: 1
Buffers: shared hit=16
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.52 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=208)
Index Cond: (id = ts.release_id)
Buffers: shared hit=624
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref (cost=0.28..1.30 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=208)
Index Cond: (id = ts.area_id)
Buffers: shared hit=627
-> Function Scan on jsonb_each pa (cost=0.00..1.00 rows=100 width=64) (actual time=0.005..0.006 rows=5 loops=208)
Planning Time: 1.392 ms
Execution Time: 9.613 ms
Planning Time: 0.000 ms
Execution Time: 129.140 ms
-> Distributed Subplan 45_7
Subplan Duration: 83.91 ms
Intermediate Data Size: 2264 kB
Result destination: Send to 1 nodes
-> WindowAgg (cost=8304.82..10304.82 rows=100000 width=722) (actual time=54.107..61.387 rows=13582 loops=1)
-> Sort (cost=8304.82..8554.82 rows=100000 width=714) (actual time=51.354..52.095 rows=13582 loops=1)
Sort Key: remote_scan.worker_column_10, remote_scan.date DESC
Sort Method: quicksort Memory: 3992kB
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=714) (actual time=43.747..45.949 rows=13582 loops=1)
Task Count: 32
Tuple data received from nodes: 2350 kB
Tasks Shown: One of 32
-> Task
Tuple data received from node: 74 kB
Node: host=private-w10.[REDACTED] port=5432 dbname=citus
-> Nested Loop (cost=1.02..503.56 rows=2 width=171) (actual time=0.058..4.986 rows=429 loops=1)
Buffers: shared hit=3194
-> Nested Loop (cost=0.73..502.73 rows=2 width=199) (actual time=0.046..4.435 rows=429 loops=1)
Buffers: shared hit=3191
-> Nested Loop (cost=0.45..500.32 rows=2 width=166) (actual time=0.040..3.568 rows=429 loops=1)
Buffers: shared hit=1903
-> Index Scan Backward using time_series_p2021_11_18_msoa_area_id_date_idx_196187 on time_series_p2021_11_18_msoa_196187 ts (cost=0.29..494.15 rows=229 width=140) (actual time=0.012..2.661 rows=1759 loops=1)
Index Cond: (date > (now() - '10 days'::interval))
Buffers: shared hit=1887
-> Memoize (cost=0.15..0.24 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=1759)
Cache Key: ts.metric_id
Hits: 1751 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=16
-> Index Scan using metric_reference_pkey_102010 on metric_reference_102010 mr (cost=0.14..0.23 rows=1 width=34) (actual time=0.003..0.003 rows=0 loops=8)
Index Cond: (id = ts.metric_id)
Filter: ((metric)::text = ANY ('{cumVaccinationFirstDoseUptakeByVaccinationDatePercentage,cumVaccinationSecondDoseUptakeByVaccinationDatePercentage}'::text[]))
Rows Removed by Filter: 1
Buffers: shared hit=16
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref (cost=0.28..1.21 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=429)
Index Cond: (id = ts.area_id)
Buffers: shared hit=1288
-> Memoize (cost=0.29..0.51 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=429)
Cache Key: ts.release_id
Hits: 428 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=3
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.50 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = ts.release_id)
Buffers: shared hit=3
Planning Time: 0.792 ms
Execution Time: 5.225 ms
Planning Time: 0.000 ms
Execution Time: 62.570 ms
-> Distributed Subplan 45_8
Subplan Duration: 1146.36 ms
Intermediate Data Size: 16 MB
Result destination: Send to 1 nodes
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=899.905..907.542 rows=113324 loops=1)
Task Count: 1
Tuple data received from nodes: 11 MB
Tasks Shown: All
-> Task
Tuple data received from node: 11 MB
Node: host=private-w10.[REDACTED] port=5432 dbname=citus
-> HashAggregate (cost=4635.13..4890.37 rows=25524 width=492) (actual time=526.604..578.655 rows=113324 loops=1)
Group Key: intermediate_result.area_id, intermediate_result.release_date, intermediate_result.metric, intermediate_result.priority, intermediate_result.area_code, intermediate_result.area_type, intermediate_result.area_name, intermediate_result.date, intermediate_result.value, intermediate_result.rank
Batches: 1 Memory Usage: 30761kB
-> Append (cost=0.00..3997.03 rows=25524 width=492) (actual time=4.156..419.821 rows=113324 loops=1)
-> Function Scan on read_intermediate_result intermediate_result (cost=0.00..66.24 rows=562 width=718) (actual time=4.154..4.376 rows=3029 loops=1)
-> WindowAgg (cost=1632.20..1836.73 rows=9090 width=718) (actual time=244.043..304.515 rows=53812 loops=1)
-> Sort (cost=1632.20..1654.93 rows=9090 width=710) (actual time=243.747..252.384 rows=53812 loops=1)
Sort Key: intermediate_result_1.metric, intermediate_result_1.priority, intermediate_result_1.date DESC
Sort Method: quicksort Memory: 9145kB
-> Function Scan on read_intermediate_result intermediate_result_1 (cost=0.00..1034.53 rows=9090 width=710) (actual time=69.890..78.832 rows=53812 loops=1)
-> Function Scan on read_intermediate_result intermediate_result_2 (cost=0.00..217.01 rows=1843 width=718) (actual time=11.935..12.641 rows=8946 loops=1)
" -> Subquery Scan on ""*SELECT* 4"" (cost=0.00..1179.67 rows=9447 width=492) (actual time=35.783..65.405 rows=33955 loops=1)"
-> Function Scan on read_intermediate_result intermediate_result_3 (cost=0.00..1061.58 rows=9447 width=464) (actual time=35.780..58.587 rows=33955 loops=1)
" -> Subquery Scan on ""*SELECT* 5"" (cost=0.00..454.81 rows=4582 width=492) (actual time=17.368..22.506 rows=13582 loops=1)"
-> Function Scan on read_intermediate_result intermediate_result_4 (cost=0.00..397.53 rows=4582 width=464) (actual time=17.362..18.565 rows=13582 loops=1)
Planning Time: 0.550 ms
Execution Time: 622.887 ms
Planning Time: 0.000 ms
Execution Time: 915.496 ms
Task Count: 1
Tuple data received from nodes: 11 MB
Tasks Shown: All
-> Task
Tuple data received from node: 11 MB
Node: host=private-w7.[REDACTED] port=5432 dbname=citus
-> HashAggregate (cost=3404.92..3405.07 rows=5 width=100) (actual time=691.561..826.178 rows=7608 loops=1)
Group Key: intermediate_result.release_date, intermediate_result.area_id
Batches: 1 Memory Usage: 106921kB
-> Function Scan on read_intermediate_result intermediate_result (cost=0.00..2926.30 rows=31908 width=484) (actual time=132.133..156.569 rows=61789 loops=1)
Filter: (rank = 1)
Rows Removed by Filter: 51535
Planning Time: 0.157 ms
Execution Time: 861.438 ms
Planning:
Buffers: shared hit=588
Planning Time: 31.779 ms
Execution Time: 3203.033 ms
Raw
query_plan-optimised.txt
QUERY PLAN
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=417.217..418.015 rows=7608 loops=1)
-> Distributed Subplan 54_1
Subplan Duration: 713.40 ms
Intermediate Data Size: 11 MB
Result destination: Send to 1 nodes
-> WindowAgg (cost=8304.82..10554.82 rows=100000 width=492) (actual time=557.916..628.478 rows=79369 loops=1)
-> Sort (cost=8304.82..8554.82 rows=100000 width=484) (actual time=557.751..565.433 rows=79369 loops=1)
Sort Key: remote_scan.metric, remote_scan.priority, remote_scan.date DESC
Sort Method: quicksort Memory: 16207kB
-> Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=484) (actual time=373.592..385.072 rows=79369 loops=1)
Task Count: 32
Tuple data received from nodes: 7524 kB
Tasks Shown: One of 32
-> Task
Tuple data received from node: 242 kB
Node: host=private-w10.[REDACTED] port=5432 dbname=citus
-> Subquery Scan on data_inner (cost=16541.24..16557.42 rows=809 width=484) (actual time=78.650..80.076 rows=2544 loops=1)
Buffers: shared hit=21058
-> HashAggregate (cost=16541.24..16549.33 rows=809 width=550) (actual time=78.648..79.690 rows=2544 loops=1)
" Group Key: ts.hash, (CASE WHEN ((ref.area_type)::text = 'overview'::text) THEN 'UK'::text ELSE (ts.area_id)::text END), ((rr.""timestamp"")::date), mr.metric, ap.priority, ref.area_code, ref.area_type, ref.area_name, ts.date, (CASE WHEN ((ts.payload ->> 'value'::text) = 'UP'::text) THEN '0'::numeric WHEN ((ts.payload ->> 'value'::text) = 'DOWN'::text) THEN '180'::numeric WHEN ((ts.payload ->> 'value'::text) = 'SAME'::text) THEN '90'::numeric WHEN ((mr.metric)::text ~~* ANY ('{%percentage%,%rate%,%transmission%}'::text[])) THEN round(((ts.payload ->> 'value'::text))::numeric, 1) ELSE ((round(((ts.payload ->> 'value'::text))::numeric, 0))::integer)::numeric END)"
Batches: 1 Memory Usage: 1161kB
Buffers: shared hit=21058
-> Append (cost=309.75..16521.01 rows=809 width=550) (actual time=1.474..76.084 rows=2544 loops=1)
Buffers: shared hit=21058
-> Hash Join (cost=309.75..871.98 rows=19 width=165) (actual time=1.474..2.704 rows=85 loops=1)
Hash Cond: ((ref.area_type)::text = (ap.area_type)::text)
Buffers: shared hit=950
-> Nested Loop (cost=308.57..869.71 rows=19 width=203) (actual time=1.431..2.306 rows=85 loops=1)
Buffers: shared hit=949
-> Nested Loop (cost=308.29..862.51 rows=19 width=199) (actual time=1.421..2.168 rows=85 loops=1)
" Join Filter: ((((ref.area_type)::text = 'overview'::text) AND ((mr.metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection,transmissionRateMin,transmissionRateMax,newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingRate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[]))) OR ((mr.metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,transmissionRateMin,transmissionRateMax}'::text[])) OR (((ref.area_type)::text = 'nation'::text) AND ((mr.metric)::text = ANY ('{newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection}'::text[]))) OR ((""left""((ref.area_code)::text, 1) = 'W'::text) AND ((mr.metric)::text = ANY ('{newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate}'::text[]))) OR ((""left""((ref.area_code)::text, 1) = ANY ('{W,N,E,S}'::text[])) AND ((mr.metric)::text = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[]))))"
Rows Removed by Join Filter: 36
Buffers: shared hit=694
-> Hash Join (cost=307.92..746.53 rows=154 width=166) (actual time=1.408..1.858 rows=121 loops=1)
Hash Cond: (ts.metric_id = mr.id)
Buffers: shared hit=331
-> Bitmap Heap Scan on time_series_p2021_11_18_other_196143 ts (cost=290.23..727.36 rows=549 width=140) (actual time=1.133..1.574 rows=499 loops=1)
Recheck Cond: (date > (now() - '18 days'::interval))
Filter: ((partition_id)::text = '2021_11_18|other'::text)
Heap Blocks: exact=266
Buffers: shared hit=326
-> Bitmap Index Scan on time_series_p2021_11_18_other_area_id_date_idx_196143 (cost=0.00..290.10 rows=549 width=0) (actual time=1.095..1.095 rows=499 loops=1)
Index Cond: (date > (now() - '18 days'::interval))
Buffers: shared hit=60
-> Hash (cost=16.95..16.95 rows=59 width=34) (actual time=0.198..0.200 rows=33 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Buffers: shared hit=5
-> Seq Scan on metric_reference_102010 mr (cost=0.08..16.95 rows=59 width=34) (actual time=0.023..0.190 rows=33 loops=1)
Filter: (((metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection,transmissionRateMin,transmissionRateMax,newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingRate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[])) OR ((metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection,transmissionRateMin,transmissionRateMax}'::text[])) OR ((metric)::text = ANY ('{newVirusTests,newVirusTestsChange,newVirusTestsChangePercentage,newVirusTestsRollingSum,newVirusTestsDirection}'::text[])) OR ((metric)::text = ANY ('{newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate}'::text[])) OR ((metric)::text = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[])))
Rows Removed by Filter: 182
Buffers: shared hit=5
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref (cost=0.28..0.68 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=121)
Index Cond: (id = ts.area_id)
Buffers: shared hit=363
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr (cost=0.28..0.38 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=85)
Index Cond: (id = ts.release_id)
Buffers: shared hit=255
-> Hash (cost=1.08..1.08 rows=8 width=12) (actual time=0.014..0.015 rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on area_priorities_126139 ap (cost=0.00..1.08 rows=8 width=12) (actual time=0.007..0.009 rows=8 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=68.96..14068.21 rows=773 width=165) (actual time=0.799..62.503 rows=1731 loops=1)
Buffers: shared hit=13377
-> Nested Loop (cost=68.82..14009.07 rows=773 width=148) (actual time=0.779..55.493 rows=1731 loops=1)
Buffers: shared hit=13373
-> Hash Join (cost=68.53..13751.52 rows=773 width=115) (actual time=0.773..52.562 rows=1731 loops=1)
Hash Cond: (ts_1.release_id = rr_1.id)
Buffers: shared hit=8174
-> Hash Join (cost=14.72..13695.68 rows=773 width=111) (actual time=0.269..51.559 rows=1731 loops=1)
Hash Cond: (ts_1.metric_id = mr_1.id)
Buffers: shared hit=8161
-> Append (cost=0.43..13664.59 rows=6270 width=85) (actual time=0.119..50.430 rows=5861 loops=1)
Buffers: shared hit=8156
-> Index Scan using time_series_p2021_11_18_ltla_partition_id_area__ee4e6e61_196079 on time_series_p2021_11_18_ltla_196079 ts_2 (cost=0.43..8901.82 rows=4107 width=85) (actual time=0.119..32.041 rows=3814 loops=1)
Index Cond: (((partition_id)::text = ANY ('{2021_11_18|utla,2021_11_18|ltla}'::text[])) AND (date > (now() - '10 days'::interval)))
Buffers: shared hit=5316
-> Index Scan using time_series_p2021_11_18_utla_partition_id_area__8470f11b_196111 on time_series_p2021_11_18_utla_196111 ts_3 (cost=0.42..4731.42 rows=2163 width=85) (actual time=0.124..17.775 rows=2047 loops=1)
Index Cond: (((partition_id)::text = ANY ('{2021_11_18|utla,2021_11_18|ltla}'::text[])) AND (date > (now() - '10 days'::interval)))
Buffers: shared hit=2840
-> Hash (cost=13.97..13.97 rows=26 width=34) (actual time=0.141..0.143 rows=26 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
Buffers: shared hit=5
-> Seq Scan on metric_reference_102010 mr_1 (cost=0.00..13.97 rows=26 width=34) (actual time=0.016..0.134 rows=26 loops=1)
Filter: ((metric)::text = ANY ('{newPeopleVaccinatedFirstDoseByPublishDate,newPeopleVaccinatedSecondDoseByPublishDate,cumPeopleVaccinatedFirstDoseByPublishDate,cumPeopleVaccinatedSecondDoseByPublishDate,cumVaccinationFirstDoseUptakeByPublishDatePercentage,cumVaccinationSecondDoseUptakeByPublishDatePercentage,newDeaths28DaysByPublishDate,newDeaths28DaysByPublishDateChange,newDeaths28DaysByPublishDateChangePercentage,newDeaths28DaysByPublishDateRollingSum,newDeaths28DaysByPublishDateDirection,newDeaths28DaysByDeathDateRollingRate,newCasesBySpecimenDateRollingSum,newCasesBySpecimenDateRollingRate,newCasesBySpecimenDateChange,newCasesBySpecimenDateChangePercentage,newCasesBySpecimenDateDirection,newCasesBySpecimenDate,newCasesByPublishDate,newCasesByPublishDateChange,newCasesByPublishDateChangePercentage,newCasesByPublishDateRollingSum,newCasesByPublishDateDirection,newPeopleVaccinatedThirdInjectionByPublishDate,cumPeopleVaccinatedThirdInjectionByPublishDate,cumVaccinationThirdInjectionUptakeByPublishDatePercentage}'::text[]))
Rows Removed by Filter: 189
Buffers: shared hit=5
-> Hash (cost=31.14..31.14 rows=1814 width=12) (actual time=0.498..0.499 rows=1817 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 95kB
Buffers: shared hit=13
-> Seq Scan on release_reference_102009 rr_1 (cost=0.00..31.14 rows=1814 width=12) (actual time=0.007..0.235 rows=1817 loops=1)
Buffers: shared hit=13
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref_1 (cost=0.28..0.33 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=1731)
Index Cond: (id = ts_1.area_id)
Buffers: shared hit=5199
-> Memoize (cost=0.14..0.16 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=1731)
Cache Key: ref_1.area_type
Hits: 1729 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=4
-> Index Only Scan using area_priorities_pkey_126139 on area_priorities_126139 ap_1 (cost=0.13..0.15 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=2)
Index Cond: (area_type = (ref_1.area_type)::text)
Heap Fetches: 2
Buffers: shared hit=4
-> Hash Join (cost=581.87..1078.70 rows=14 width=165) (actual time=2.755..5.417 rows=284 loops=1)
Hash Cond: ((ref_2.area_type)::text = (ap_2.area_type)::text)
Buffers: shared hit=2231
-> Nested Loop (cost=580.69..1076.74 rows=14 width=129) (actual time=2.714..4.452 rows=284 loops=1)
Buffers: shared hit=2230
-> Nested Loop (cost=580.41..1067.57 rows=14 width=96) (actual time=2.705..3.940 rows=284 loops=1)
Buffers: shared hit=1378
-> Hash Join (cost=580.13..1062.34 rows=14 width=92) (actual time=2.691..3.477 rows=284 loops=1)
Hash Cond: (ts_4.metric_id = mr_2.id)
Buffers: shared hit=526
-> Bitmap Heap Scan on time_series_p2021_11_18_nhstrust_196207 ts_4 (cost=571.64..1052.28 rows=586 width=66) (actual time=2.601..3.299 rows=471 loops=1)
Recheck Cond: (date > (now() - '16 days'::interval))
Filter: (((payload ->> 'value'::text) IS NOT NULL) AND ((partition_id)::text = '2021_11_18|nhstrust'::text))
Rows Removed by Filter: 133
Heap Blocks: exact=381
Buffers: shared hit=521
-> Bitmap Index Scan on time_series_p2021_11_18_nhstrust_area_id_date_idx_196207 (cost=0.00..571.49 rows=589 width=0) (actual time=2.546..2.546 rows=604 loops=1)
Index Cond: (date > (now() - '16 days'::interval))
Buffers: shared hit=140
-> Hash (cost=8.43..8.43 rows=5 width=34) (actual time=0.065..0.066 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=5
-> Seq Scan on metric_reference_102010 mr_2 (cost=0.00..8.43 rows=5 width=34) (actual time=0.039..0.063 rows=5 loops=1)
Filter: ((metric)::text = ANY ('{newAdmissions,newAdmissionsChange,newAdmissionsChangePercentage,newAdmissionsRollingSum,newAdmissionsDirection}'::text[]))
Rows Removed by Filter: 210
Buffers: shared hit=5
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr_2 (cost=0.28..0.37 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=284)
Index Cond: (id = ts_4.release_id)
Buffers: shared hit=852
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref_2 (cost=0.28..0.65 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=284)
Index Cond: (id = ts_4.area_id)
Buffers: shared hit=852
-> Hash (cost=1.08..1.08 rows=8 width=12) (actual time=0.020..0.021 rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on area_priorities_126139 ap_2 (cost=0.00..1.08 rows=8 width=12) (actual time=0.015..0.016 rows=8 loops=1)
Buffers: shared hit=1
" -> Subquery Scan on ""*SELECT* 4"" (cost=8.19..490.01 rows=2 width=194) (actual time=0.135..5.066 rows=444 loops=1)"
Buffers: shared hit=4500
-> Nested Loop (cost=8.19..489.99 rows=2 width=166) (actual time=0.133..4.939 rows=444 loops=1)
Buffers: shared hit=4500
-> Nested Loop (cost=7.91..487.34 rows=2 width=195) (actual time=0.114..3.782 rows=444 loops=1)
Buffers: shared hit=3168
-> Hash Join (cost=7.63..486.30 rows=2 width=191) (actual time=0.101..3.112 rows=444 loops=1)
Hash Cond: (ts_5.metric_id = mr_3.id)
Buffers: shared hit=1836
-> Index Scan Backward using time_series_p2021_11_18_msoa_area_id_date_idx_196175 on time_series_p2021_11_18_msoa_196175 ts_5 (cost=0.29..478.41 rows=207 width=165) (actual time=0.049..2.823 rows=1707 loops=1)
Index Cond: (date > (now() - '10 days'::interval))
Filter: ((partition_id)::text = '2021_11_18|msoa'::text)
Buffers: shared hit=1830
-> Hash (cost=7.32..7.32 rows=2 width=34) (actual time=0.032..0.033 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=6
-> Bitmap Heap Scan on metric_reference_102010 mr_3 (cost=4.56..7.32 rows=2 width=34) (actual time=0.025..0.028 rows=2 loops=1)
Recheck Cond: ((metric)::text = ANY ('{cumVaccinationFirstDoseUptakeByVaccinationDatePercentage,cumVaccinationSecondDoseUptakeByVaccinationDatePercentage}'::text[]))
Heap Blocks: exact=2
Buffers: shared hit=6
-> Bitmap Index Scan on metricref_metric_id_idx_102010 (cost=0.00..4.55 rows=2 width=0) (actual time=0.021..0.022 rows=2 loops=1)
Index Cond: ((metric)::text = ANY ('{cumVaccinationFirstDoseUptakeByVaccinationDatePercentage,cumVaccinationSecondDoseUptakeByVaccinationDatePercentage}'::text[]))
Buffers: shared hit=4
-> Index Scan using release_reference_pkey_102009 on release_reference_102009 rr_3 (cost=0.28..0.52 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=444)
Index Cond: (id = ts_5.release_id)
Buffers: shared hit=1332
-> Index Scan using area_reference_id_key_102008 on area_reference_102008 ref_3 (cost=0.28..1.30 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=444)
Index Cond: (id = ts_5.area_id)
Buffers: shared hit=1332
" -> Subquery Scan on ""*SELECT* 5"" (cost=0.00..0.00 rows=1 width=235) (actual time=0.002..0.003 rows=0 loops=1)"
-> Result (cost=0.00..0.00 rows=0 width=207) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
Planning Time: 6.284 ms
Execution Time: 81.426 ms
Planning Time: 0.000 ms
Execution Time: 634.460 ms
Task Count: 1
Tuple data received from nodes: 5463 kB
Tasks Shown: All
-> Task
Tuple data received from node: 5463 kB
Node: host=private-w6.[REDACTED] port=5432 dbname=citus
-> HashAggregate (cost=2368.03..2368.18 rows=5 width=100) (actual time=319.196..380.454 rows=7608 loops=1)
Group Key: intermediate_result.release_date, intermediate_result.area_id
Batches: 1 Memory Usage: 49577kB
Buffers: shared hit=3
-> Function Scan on read_intermediate_result intermediate_result (cost=0.00..2035.16 rows=22191 width=484) (actual time=85.935..96.803 rows=27818 loops=1)
Filter: (rank = 1)
Rows Removed by Filter: 51551
Buffers: shared hit=3
Planning Time: 0.217 ms
Execution Time: 392.393 ms
Planning:
Buffers: shared hit=342
Planning Time: 28.600 ms
Execution Time: 1134.242 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment