explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qr6I

Settings
# exclusive inclusive rows x rows loops node
1. 1.530 39,953.037 ↓ 637.0 637 1

Sort (cost=109,950.21..109,950.21 rows=1 width=161) (actual time=39,952.798..39,953.037 rows=637 loops=1)

  • Sort Key: q.zone DESC, (date_part('epoch'::text, (max(q."time") - max(q.start_time)))) DESC, (concat(g.name, ' [', g.cms_id, ']'))
  • Sort Method: quicksort Memory: 117kB
2.          

CTE q_patch_details

3. 676.577 2,174.918 ↓ 44.3 851,430 1

Nested Loop (cost=6,819.35..109,078.67 rows=19,220 width=117) (actual time=50.314..2,174.918 rows=851,430 loops=1)

4. 9.052 63.498 ↓ 98.7 3,651 1

GroupAggregate (cost=6,818.79..6,819.90 rows=37 width=97) (actual time=49.803..63.498 rows=3,651 loops=1)

  • Group Key: l.metric_name, ((l.labels ->> 'cms_id'::text)), ((l.labels ->> 'zone'::text))
5. 28.220 54.446 ↓ 256.6 9,495 1

Sort (cost=6,818.79..6,818.88 rows=37 width=97) (actual time=49.795..54.446 rows=9,495 loops=1)

  • Sort Key: l.metric_name, ((l.labels ->> 'cms_id'::text)), ((l.labels ->> 'zone'::text))
  • Sort Method: quicksort Memory: 1442kB
6. 21.653 26.226 ↓ 256.6 9,495 1

Bitmap Heap Scan on metrics_labels l (cost=521.91..6,817.82 rows=37 width=97) (actual time=4.726..26.226 rows=9,495 loops=1)

  • Recheck Cond: (((metric_name = 'gfn_gamepipeline_metrics'::text) AND (metric_name = ANY ('{gfn_gamepipeline_metrics,patching_state}'::text[])) AND (start_time >= ('2019-08-06 22:56:18.034+00'::timestamp with time zone - '7 days'::interval)) AND (start_time <= '2019-08-07 22:56:18.034+00'::timestamp with time zone)) OR ((metric_name = 'patching_state'::text) AND (metric_name = ANY ('{gfn_gamepipeline_metrics,patching_state}'::text[])) AND (start_time >= ('2019-08-06 22:56:18.034+00'::timestamp with time zone - '7 days'::interval)) AND (start_time <= '2019-08-07 22:56:18.034+00'::timestamp with time zone)))
  • Filter: ((NOT (labels @> '{"patch_id": ""}'::jsonb)) AND (start_time >= ('2019-08-06 22:56:18.034+00'::timestamp with time zone - '7 days'::interval)) AND ((labels ->> 'public'::text) = 'true'::text))
  • Rows Removed by Filter: 12874
  • Heap Blocks: exact=1228
7. 0.002 4.573 ↓ 0.0 0 1

BitmapOr (cost=521.91..521.91 rows=8,321 width=0) (actual time=4.573..4.573 rows=0 loops=1)

8. 2.477 2.477 ↓ 2.2 11,193 1

Bitmap Index Scan on metrics_labels_metric_name_new_idx (cost=0.00..318.44 rows=5,096 width=0) (actual time=2.477..2.477 rows=11,193 loops=1)

  • Index Cond: ((metric_name = 'gfn_gamepipeline_metrics'::text) AND (metric_name = ANY ('{gfn_gamepipeline_metrics,patching_state}'::text[])) AND (start_time >= ('2019-08-06 22:56:18.034+00'::timestamp with time zone - '7 days'::interval)) AND (start_time <= '2019-08-07 22:56:18.034+00'::timestamp with time zone))
9. 2.094 2.094 ↓ 3.5 11,176 1

Bitmap Index Scan on metrics_labels_metric_name_new_idx (cost=0.00..203.46 rows=3,225 width=0) (actual time=2.094..2.094 rows=11,176 loops=1)

  • Index Cond: ((metric_name = 'patching_state'::text) AND (metric_name = ANY ('{gfn_gamepipeline_metrics,patching_state}'::text[])) AND (start_time >= ('2019-08-06 22:56:18.034+00'::timestamp with time zone - '7 days'::interval)) AND (start_time <= '2019-08-07 22:56:18.034+00'::timestamp with time zone))
10. 620.670 1,434.843 ↓ 10.6 233 3,651

Append (cost=0.56..2,763.52 rows=22 width=20) (actual time=0.039..0.393 rows=233 loops=3,651)

11. 62.067 62.067 ↓ 16.0 16 3,651

Index Scan using _hyper_14_1439_chunk_metrics_values_labels_id_idx on _hyper_14_1439_chunk (cost=0.56..187.48 rows=1 width=20) (actual time=0.005..0.017 rows=16 loops=3,651)

  • Index Cond: ((labels_id = (max(l.id))) AND ("time" >= '2019-08-06 22:56:18.034+00'::timestamp with time zone) AND ("time" <= '2019-08-07 22:56:18.034+00'::timestamp with time zone))
  • Filter: (((l.metric_name = 'gfn_gamepipeline_metrics'::text) AND (value = '2'::double precision)) OR (l.metric_name = 'patching_state'::text))
  • Rows Removed by Filter: 3
12. 752.106 752.106 ↓ 10.3 217 3,651

Index Scan using _hyper_14_1449_chunk_metrics_values_labels_id_idx on _hyper_14_1449_chunk (cost=0.56..2,576.05 rows=21 width=20) (actual time=0.033..0.206 rows=217 loops=3,651)

  • Index Cond: ((labels_id = (max(l.id))) AND ("time" >= '2019-08-06 22:56:18.034+00'::timestamp with time zone) AND ("time" <= '2019-08-07 22:56:18.034+00'::timestamp with time zone))
  • Filter: (((l.metric_name = 'gfn_gamepipeline_metrics'::text) AND (value = '2'::double precision)) OR (l.metric_name = 'patching_state'::text))
  • Rows Removed by Filter: 74
13. 2,672.911 39,951.507 ↓ 637.0 637 1

GroupAggregate (cost=871.47..871.53 rows=1 width=161) (actual time=34,273.192..39,951.507 rows=637 loops=1)

  • Group Key: (concat(g.name, ' [', g.cms_id, ']')), g.cms_id, q.zone
14. 9,127.061 37,278.596 ↓ 4,457,386.0 4,457,386 1

Sort (cost=871.47..871.47 rows=1 width=89) (actual time=34,263.517..37,278.596 rows=4,457,386 loops=1)

  • Sort Key: (concat(g.name, ' [', g.cms_id, ']')), g.cms_id, q.zone DESC
  • Sort Method: external merge Disk: 335840kB
15. 8,117.532 28,151.535 ↓ 4,457,386.0 4,457,386 1

Nested Loop (cost=434.40..871.46 rows=1 width=89) (actual time=3,412.055..28,151.535 rows=4,457,386 loops=1)

16. 2,973.654 6,661.845 ↓ 4,457,386.0 4,457,386 1

Hash Join (cost=434.13..870.91 rows=1 width=112) (actual time=3,412.030..6,661.845 rows=4,457,386 loops=1)

  • Hash Cond: ((q.cms_id = q_patch_state.cms_id) AND (q."time" = q_patch_state."time") AND (q.zone = q_patch_state.zone))
17. 326.568 326.568 ↓ 4,433.6 425,627 1

CTE Scan on q_patch_details q (cost=0.00..432.45 rows=96 width=80) (actual time=50.319..326.568 rows=425,627 loops=1)

  • Filter: (metric_name = 'gfn_gamepipeline_metrics'::text)
  • Rows Removed by Filter: 425803
18. 290.971 3,361.623 ↓ 4,435.4 425,803 1

Hash (cost=432.45..432.45 rows=96 width=72) (actual time=3,361.623..3,361.623 rows=425,803 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 7924kB
19. 3,070.652 3,070.652 ↓ 4,435.4 425,803 1

CTE Scan on q_patch_details q_patch_state (cost=0.00..432.45 rows=96 width=72) (actual time=1,486.679..3,070.652 rows=425,803 loops=1)

  • Filter: (metric_name = 'patching_state'::text)
  • Rows Removed by Filter: 425627
20. 13,372.158 13,372.158 ↑ 1.0 1 4,457,386

Index Scan using game_meta_info_pkey on game_meta_info g (cost=0.28..0.54 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=4,457,386)

  • Index Cond: (cms_id = q.cms_id)
Planning time : 1.175 ms
Execution time : 40,029.932 ms