explain.depesz.com

PostgreSQL's explain analyze made readable

Result: APoB

Settings
# exclusive inclusive rows x rows loops node
1. 0.248 1,214.625 ↓ 162.0 162 1

WindowAgg (cost=807.60..807.64 rows=1 width=72) (actual time=1,214.160..1,214.625 rows=162 loops=1)

2.          

CTE _qend

3. 3.662 1,213.761 ↓ 162.0 162 1

Nested Loop (cost=806.83..807.57 rows=1 width=64) (actual time=197.553..1,213.761 rows=162 loops=1)

  • Join Filter: (q_metrics.name = _q.name)
  • Rows Removed by Join Filter: 7821
4.          

CTE _q

5. 14.043 163.119 ↓ 578.7 14,468 1

GroupAggregate (cost=805.39..806.14 rows=25 width=48) (actual time=143.250..163.119 rows=14,468 loops=1)

  • Group Key: (time_bucket('00:15:00'::interval, m."time")), (concat((l.labels ->> 'zone'::text), ' : ', (l.labels ->> 'error_code'::text)))
6. 26.189 149.076 ↓ 578.7 14,468 1

Sort (cost=805.39..805.45 rows=25 width=48) (actual time=143.242..149.076 rows=14,468 loops=1)

  • Sort Key: (time_bucket('00:15:00'::interval, m."time")), (concat((l.labels ->> 'zone'::text), ' : ', (l.labels ->> 'error_code'::text)))
  • Sort Method: quicksort Memory: 1515kB
7. 25.093 122.887 ↓ 578.7 14,468 1

Nested Loop (cost=198.44..804.81 rows=25 width=48) (actual time=43.183..122.887 rows=14,468 loops=1)

8. 34.043 63.144 ↓ 110.0 110 1

Bitmap Heap Scan on metrics_labels l (cost=198.01..199.14 rows=1 width=70) (actual time=43.144..63.144 rows=110 loops=1)

  • Recheck Cond: ((labels @> '{"cms_id": "all"}'::jsonb) AND (labels @> '{"zone": "all"}'::jsonb))
  • Rows Removed by Index Recheck: 66324
  • Filter: ((metric_name = 'error_codes_total'::text) AND (((labels ->> 'error_code'::text) ~~ '8%'::text) OR ((labels ->> 'error_code'::text) ~~ 'C%'::text)))
  • Rows Removed by Filter: 387
  • Heap Blocks: exact=6329
9. 29.101 29.101 ↓ 66,821.0 66,821 1

Bitmap Index Scan on metrics_labels_labels_idx (cost=0.00..198.01 rows=1 width=0) (actual time=29.100..29.101 rows=66,821 loops=1)

  • Index Cond: ((labels @> '{"cms_id": "all"}'::jsonb) AND (labels @> '{"zone": "all"}'::jsonb))
10. 11.330 34.650 ↑ 3.0 132 110

Custom Scan (ChunkAppend) on metrics_values m (cost=0.43..601.44 rows=397 width=20) (actual time=0.022..0.315 rows=132 loops=110)

  • Chunks excluded during startup: 87
11. 1.540 1.540 ↑ 2.8 8 110

Index Scan using _hyper_48_1755_chunk_metrics_values_labels_id_idx on _hyper_48_1755_chunk m_1 (cost=0.43..26.22 rows=22 width=20) (actual time=0.005..0.014 rows=8 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
12. 2.090 2.090 ↑ 2.1 12 110

Index Scan using _hyper_48_1861_chunk_metrics_values_labels_id_idx on _hyper_48_1861_chunk m_2 (cost=0.43..29.57 rows=25 width=20) (actual time=0.005..0.019 rows=12 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
13. 1.540 1.540 ↑ 2.7 9 110

Index Scan using _hyper_48_1713_chunk_metrics_values_labels_id_idx on _hyper_48_1713_chunk m_3 (cost=0.43..28.45 rows=24 width=20) (actual time=0.004..0.014 rows=9 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
14. 2.200 2.200 ↑ 1.6 13 110

Index Scan using _hyper_48_1875_chunk_metrics_values_labels_id_idx on _hyper_48_1875_chunk m_4 (cost=0.43..25.06 rows=21 width=20) (actual time=0.005..0.020 rows=13 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
15. 1.540 1.540 ↑ 2.9 8 110

Index Scan using _hyper_48_1768_chunk_metrics_values_labels_id_idx on _hyper_48_1768_chunk m_5 (cost=0.43..27.35 rows=23 width=20) (actual time=0.004..0.014 rows=8 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
16. 1.870 1.870 ↑ 2.4 12 110

Index Scan using _hyper_48_1812_chunk_metrics_values_labels_id_idx on _hyper_48_1812_chunk m_6 (cost=0.43..34.08 rows=29 width=20) (actual time=0.004..0.017 rows=12 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
17. 0.660 0.660 ↑ 2.5 2 110

Index Scan using _hyper_48_1686_chunk_metrics_values_labels_id_idx on _hyper_48_1686_chunk m_7 (cost=0.43..7.14 rows=5 width=20) (actual time=0.004..0.006 rows=2 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
18. 1.870 1.870 ↑ 1.7 11 110

Index Scan using _hyper_48_1893_chunk_metrics_values_labels_id_idx on _hyper_48_1893_chunk m_8 (cost=0.43..22.86 rows=19 width=20) (actual time=0.005..0.017 rows=11 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
19. 1.760 1.760 ↑ 2.5 10 110

Index Scan using _hyper_48_1783_chunk_metrics_values_labels_id_idx on _hyper_48_1783_chunk m_9 (cost=0.43..29.59 rows=25 width=20) (actual time=0.005..0.016 rows=10 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
20. 1.430 1.430 ↑ 2.8 8 110

Index Scan using _hyper_48_1740_chunk_metrics_values_labels_id_idx on _hyper_48_1740_chunk m_10 (cost=0.43..26.23 rows=22 width=20) (actual time=0.004..0.013 rows=8 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
21. 0.990 0.990 ↑ 2.4 5 110

Index Scan using _hyper_48_1843_chunk_metrics_values_labels_id_idx on _hyper_48_1843_chunk m_11 (cost=0.43..14.99 rows=12 width=20) (actual time=0.004..0.009 rows=5 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
22. 1.650 1.650 ↑ 2.6 9 110

Index Scan using _hyper_48_1700_chunk_metrics_values_labels_id_idx on _hyper_48_1700_chunk m_12 (cost=0.43..27.33 rows=23 width=20) (actual time=0.004..0.015 rows=9 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
23. 1.870 1.870 ↑ 2.2 12 110

Index Scan using _hyper_48_1797_chunk_metrics_values_labels_id_idx on _hyper_48_1797_chunk m_13 (cost=0.43..30.70 rows=26 width=20) (actual time=0.005..0.017 rows=12 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
24. 0.880 0.880 ↑ 3.0 4 110

Index Scan using _hyper_48_1827_chunk_metrics_values_labels_id_idx on _hyper_48_1827_chunk m_14 (cost=0.43..14.99 rows=12 width=20) (actual time=0.004..0.008 rows=4 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
25. 1.430 1.430 ↑ 2.8 8 110

Index Scan using _hyper_48_1726_chunk_metrics_values_labels_id_idx on _hyper_48_1726_chunk m_15 (cost=0.43..26.21 rows=22 width=20) (actual time=0.004..0.013 rows=8 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '14 days'::interval)) AND ("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone))
26. 172.165 172.165 ↓ 162.0 162 1

CTE Scan on _q q_metrics (cost=0.00..0.69 rows=1 width=48) (actual time=172.039..172.165 rows=162 loops=1)

  • Filter: (("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone) AND ("time" >= ('2019-09-06 19:53:35.193+00'::timestamp with time zone - '00:30:00'::interval)))
  • Rows Removed by Filter: 14306
27. 584.820 1,037.934 ↓ 49.0 49 162

GroupAggregate (cost=0.70..0.72 rows=1 width=40) (actual time=0.163..6.407 rows=49 loops=162)

  • Group Key: _q.name
28. 443.459 453.114 ↓ 6,821.0 6,821 162

Sort (cost=0.70..0.70 rows=1 width=40) (actual time=0.156..2.797 rows=6,821 loops=162)

  • Sort Key: _q.name
  • Sort Method: quicksort Memory: 1507kB
29. 9.655 9.655 ↓ 14,367.0 14,367 1

CTE Scan on _q (cost=0.00..0.69 rows=1 width=40) (actual time=0.028..9.655 rows=14,367 loops=1)

  • Filter: (("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone) AND ("time" >= ('2019-09-06 21:53:35.193+00'::timestamp with time zone - '14 days'::interval)))
  • Rows Removed by Filter: 101
30. 0.172 1,214.377 ↓ 162.0 162 1

WindowAgg (cost=0.03..0.05 rows=1 width=64) (actual time=1,214.146..1,214.377 rows=162 loops=1)

31. 0.181 1,214.205 ↓ 162.0 162 1

Sort (cost=0.03..0.04 rows=1 width=56) (actual time=1,214.142..1,214.205 rows=162 loops=1)

  • Sort Key: _qend."time
  • Sort Method: quicksort Memory: 37kB
32. 1,214.024 1,214.024 ↓ 162.0 162 1

CTE Scan on _qend (cost=0.00..0.02 rows=1 width=56) (actual time=197.556..1,214.024 rows=162 loops=1)

Planning time : 14.105 ms
Execution time : 1,216.304 ms