explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xQTp

Settings
# exclusive inclusive rows x rows loops node
1. 0.635 2,927.484 ↓ 429.0 429 1

WindowAgg (cost=812.26..812.30 rows=1 width=72) (actual time=2,926.260..2,927.484 rows=429 loops=1)

2.          

CTE _qend

3. 9.825 2,925.237 ↓ 429.0 429 1

Nested Loop (cost=811.46..812.23 rows=1 width=64) (actual time=202.206..2,925.237 rows=429 loops=1)

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

CTE _q

5. 14.312 167.325 ↓ 563.8 14,659 1

GroupAggregate (cost=809.96..810.74 rows=26 width=48) (actual time=147.071..167.325 rows=14,659 loops=1)

  • Group Key: (time_bucket('00:15:00'::interval, m."time")), (concat((l.labels ->> 'zone'::text), ' : ', (l.labels ->> 'error_code'::text)))
6. 26.605 153.013 ↓ 563.8 14,659 1

Sort (cost=809.96..810.02 rows=26 width=48) (actual time=147.065..153.013 rows=14,659 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: 1530kB
7. 25.827 126.408 ↓ 563.8 14,659 1

Nested Loop (cost=198.44..809.34 rows=26 width=48) (actual time=44.461..126.408 rows=14,659 loops=1)

8. 34.675 64.831 ↓ 110.0 110 1

Bitmap Heap Scan on metrics_labels l (cost=198.01..199.14 rows=1 width=70) (actual time=44.415..64.831 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. 30.156 30.156 ↓ 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=30.156..30.156 rows=66,821 loops=1)

  • Index Cond: ((labels @> '{"cms_id": "all"}'::jsonb) AND (labels @> '{"zone": "all"}'::jsonb))
10. 11.440 35.750 ↑ 3.0 133 110

Custom Scan (ChunkAppend) on metrics_values m (cost=0.43..605.93 rows=401 width=20) (actual time=0.023..0.325 rows=133 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 15: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 15: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.650 1.650 ↑ 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.015 rows=9 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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.310 2.310 ↑ 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.021 rows=13 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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.005..0.014 rows=8 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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.980 1.980 ↑ 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.005..0.018 rows=12 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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.880 0.880 ↑ 2.2 4 110

Index Scan using _hyper_48_1686_chunk_metrics_values_labels_id_idx on _hyper_48_1686_chunk m_7 (cost=0.43..11.63 rows=9 width=20) (actual time=0.004..0.008 rows=4 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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.980 1.980 ↑ 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.018 rows=11 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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 15: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.540 1.540 ↑ 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.005..0.014 rows=8 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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. 1.100 1.100 ↑ 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.010 rows=5 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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 15: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.980 1.980 ↑ 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.018 rows=12 loops=110)

  • Index Cond: ((labels_id = l.id) AND ("time" >= ('2019-09-06 15: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 15: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 15: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. 176.247 176.247 ↓ 429.0 429 1

CTE Scan on _q q_metrics (cost=0.00..0.72 rows=1 width=48) (actual time=175.891..176.247 rows=429 loops=1)

  • Filter: (("time" <= '2019-09-06 21:53:35.193+00'::timestamp with time zone) AND ("time" >= ('2019-09-06 15:53:35.193+00'::timestamp with time zone - '00:30:00'::interval)))
  • Rows Removed by Filter: 14230
27. 1,573.143 2,739.165 ↓ 50.0 50 429

GroupAggregate (cost=0.73..0.75 rows=1 width=40) (actual time=0.067..6.385 rows=50 loops=429)

  • Group Key: _q.name
28. 1,155.879 1,166.022 ↓ 6,899.0 6,899 429

Sort (cost=0.73..0.73 rows=1 width=40) (actual time=0.060..2.718 rows=6,899 loops=429)

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

CTE Scan on _q (cost=0.00..0.72 rows=1 width=40) (actual time=0.073..10.143 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: 292
30. 0.442 2,926.849 ↓ 429.0 429 1

WindowAgg (cost=0.03..0.05 rows=1 width=64) (actual time=2,926.245..2,926.849 rows=429 loops=1)

31. 0.468 2,926.407 ↓ 429.0 429 1

Sort (cost=0.03..0.04 rows=1 width=56) (actual time=2,926.240..2,926.407 rows=429 loops=1)

  • Sort Key: _qend."time
  • Sort Method: quicksort Memory: 58kB
32. 2,925.939 2,925.939 ↓ 429.0 429 1

CTE Scan on _qend (cost=0.00..0.02 rows=1 width=56) (actual time=202.209..2,925.939 rows=429 loops=1)

Planning time : 14.220 ms
Execution time : 2,929.332 ms