explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PVyq

Settings
# exclusive inclusive rows x rows loops node
1. 0.135 8,821.360 ↓ 148.0 148 1

Sort (cost=104,891.55..104,891.56 rows=1 width=48) (actual time=8,821.305..8,821.360 rows=148 loops=1)

  • Sort Key: (time_bucket_gapfill('00:10:00'::interval, _qw."time"))
  • Sort Method: quicksort Memory: 45kB
2.          

CTE q_patch_details

3. 1,609.462 8,792.656 ↓ 2,276.0 2,276 1

Nested Loop (cost=100,516.65..104,888.91 rows=1 width=124) (actual time=329.477..8,792.656 rows=2,276 loops=1)

  • Join Filter: (((l_1.labels ->> 'patch_id'::text)) = (l.labels ->> 'patch_id'::text))
  • Rows Removed by Join Filter: 220765
4. 1,259.040 3,893.628 ↓ 4,351.3 1,644,783 1

Nested Loop (cost=100,516.22..104,699.60 rows=378 width=128) (actual time=328.840..3,893.628 rows=1,644,783 loops=1)

5. 22.930 449.132 ↓ 61.3 8,216 1

Nested Loop (cost=100,515.81..103,681.52 rows=134 width=116) (actual time=328.825..449.132 rows=8,216 loops=1)

6. 14.841 348.754 ↓ 293.4 12,908 1

GroupAggregate (cost=100,515.39..100,516.82 rows=44 width=108) (actual time=328.072..348.754 rows=12,908 loops=1)

  • Group Key: ((l_1.labels ->> 'cms_id'::text)), ((l_1.labels ->> 'zone'::text)), ((l_1.labels ->> 'patch_id'::text))
7. 38.908 333.913 ↓ 293.4 12,908 1

Sort (cost=100,515.39..100,515.50 rows=44 width=108) (actual time=328.064..333.913 rows=12,908 loops=1)

  • Sort Key: ((l_1.labels ->> 'cms_id'::text)), ((l_1.labels ->> 'zone'::text)), ((l_1.labels ->> 'patch_id'::text))
  • Sort Method: quicksort Memory: 2200kB
8. 30.491 295.005 ↓ 293.4 12,908 1

Hash Join (cost=100,116.55..100,514.19 rows=44 width=108) (actual time=61.671..295.005 rows=12,908 loops=1)

  • Hash Cond: ((l_1.labels ->> 'cms_id'::text) = _q.cms_id)
9. 205.869 253.804 ↓ 440.1 38,287 1

Bitmap Heap Scan on metrics_labels l_1 (cost=325.03..722.10 rows=87 width=224) (actual time=49.997..253.804 rows=38,287 loops=1)

  • Recheck Cond: (labels @> '{"public": "true"}'::jsonb)
  • Rows Removed by Index Recheck: 135449
  • Filter: ((NOT (labels @> '{"patch_id": ""}'::jsonb)) AND (metric_name = 'gfn_gamepipeline_metrics'::text) AND ((labels ->> 'patch_id'::text) <> ALL ('{43cfed9e-769d-11e9-87dd-0cc47a95867e,d13a6d36-7bcc-11e9-892f-0cc47a95867e}'::text[])))
  • Rows Removed by Filter: 188013
  • Heap Blocks: exact=12988
10. 47.935 47.935 ↓ 1,002.1 361,749 1

Bitmap Index Scan on metrics_labels_labels_idx (cost=0.00..325.01 rows=361 width=0) (actual time=47.934..47.935 rows=361,749 loops=1)

  • Index Cond: (labels @> '{"public": "true"}'::jsonb)
11. 0.050 10.710 ↑ 1.0 100 1

Hash (cost=99,790.27..99,790.27 rows=100 width=32) (actual time=10.710..10.710 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
12. 0.077 10.660 ↑ 1.0 100 1

Subquery Scan on _q (cost=99,789.02..99,790.27 rows=100 width=32) (actual time=10.463..10.660 rows=100 loops=1)

13. 0.091 10.583 ↑ 1.0 100 1

Limit (cost=99,789.02..99,789.27 rows=100 width=48) (actual time=10.462..10.583 rows=100 loops=1)

14. 0.123 10.492 ↑ 1.8 121 1

Sort (cost=99,788.96..99,789.50 rows=214 width=48) (actual time=10.446..10.492 rows=121 loops=1)

  • Sort Key: (sum(m_2.value)) DESC
  • Sort Method: quicksort Memory: 34kB
15. 0.440 10.369 ↑ 1.7 123 1

GroupAggregate (cost=99,752.33..99,780.68 rows=214 width=48) (actual time=9.642..10.369 rows=123 loops=1)

  • Group Key: ((l_2.labels ->> 'cms_id'::text))
16. 1.152 9.929 ↑ 4.3 800 1

Sort (cost=99,752.33..99,760.89 rows=3,423 width=40) (actual time=9.632..9.929 rows=800 loops=1)

  • Sort Key: ((l_2.labels ->> 'cms_id'::text))
  • Sort Method: quicksort Memory: 86kB
17. 1.883 8.777 ↑ 4.3 800 1

Nested Loop (cost=0.99..99,551.38 rows=3,423 width=40) (actual time=0.090..8.777 rows=800 loops=1)

18. 0.298 0.298 ↑ 1.1 194 1

Index Scan using metrics_labels_metric_name_idx on metrics_labels l_2 (cost=0.55..239.44 rows=214 width=64) (actual time=0.019..0.298 rows=194 loops=1)

  • Index Cond: (metric_name = 'playtime_24hrs_duration_hours'::text)
19. 1.552 6.596 ↑ 66.5 4 194

Custom Scan (ChunkAppend) on metrics_values m_2 (cost=0.44..461.37 rows=266 width=12) (actual time=0.014..0.034 rows=4 loops=194)

  • Chunks excluded during startup: 111
20. 0.582 0.582 ↑ 6.0 1 194

Index Scan using _hyper_48_2967_chunk_metrics_values_labels_id_idx on _hyper_48_2967_chunk m_3 (cost=0.44..8.01 rows=6 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
21. 0.582 0.582 ↑ 19.0 1 194

Index Scan using _hyper_48_2985_chunk_metrics_values_labels_id_idx on _hyper_48_2985_chunk m_4 (cost=0.44..20.28 rows=19 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
22. 0.582 0.582 ↑ 23.0 1 194

Index Scan using _hyper_48_3003_chunk_metrics_values_labels_id_idx on _hyper_48_3003_chunk m_5 (cost=0.44..24.44 rows=23 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
23. 0.582 0.582 ↑ 24.0 1 194

Index Scan using _hyper_48_3021_chunk_metrics_values_labels_id_idx on _hyper_48_3021_chunk m_6 (cost=0.44..25.30 rows=24 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
24. 0.582 0.582 ↑ 20.0 1 194

Index Scan using _hyper_48_3040_chunk_metrics_values_labels_id_idx on _hyper_48_3040_chunk m_7 (cost=0.44..21.10 rows=20 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
25. 0.582 0.582 ↑ 21.0 1 194

Index Scan using _hyper_48_3060_chunk_metrics_values_labels_id_idx on _hyper_48_3060_chunk m_8 (cost=0.44..22.05 rows=21 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
26. 0.582 0.582 ↑ 20.0 1 194

Index Scan using _hyper_48_3078_chunk_metrics_values_labels_id_idx on _hyper_48_3078_chunk m_9 (cost=0.44..21.14 rows=20 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
27. 0.582 0.582 ↑ 20.0 1 194

Index Scan using _hyper_48_3095_chunk_metrics_values_labels_id_idx on _hyper_48_3095_chunk m_10 (cost=0.44..21.09 rows=20 width=12) (actual time=0.003..0.003 rows=1 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
28. 0.388 0.388 ↓ 0.0 0 194

Index Scan using _hyper_48_3112_chunk_metrics_values_labels_id_idx on _hyper_48_3112_chunk m_11 (cost=0.43..2.78 rows=2 width=12) (actual time=0.002..0.002 rows=0 loops=194)

  • Index Cond: ((labels_id = l_2.id) AND ("time" >= (date_trunc('minutes'::text, '2019-11-07 19:01:50.678+00'::timestamp with time zone) - '168:00:00'::interval)) AND ("time" <= (date_trunc('minutes'::text, '2019-11-08 01:01:50.678+00'::timestamp with time zone) + '00:01:00'::interval)))
29. 12.908 77.448 ↑ 22.0 1 12,908

Append (cost=0.42..71.70 rows=22 width=12) (actual time=0.005..0.006 rows=1 loops=12,908)

30. 25.816 25.816 ↓ 0.0 0 12,908

Index Scan using _hyper_14_3109_chunk_metrics_values_labels_id_idx on _hyper_14_3109_chunk _mv (cost=0.42..20.72 rows=9 width=12) (actual time=0.002..0.002 rows=0 loops=12,908)

  • Index Cond: ((labels_id = (min(l_1.id))) AND ("time" >= '2019-11-07 19:01:50.678+00'::timestamp with time zone) AND ("time" <= '2019-11-08 01:01:50.678+00'::timestamp with time zone))
  • Filter: (value = '2'::double precision)
31. 38.724 38.724 ↑ 13.0 1 12,908

Index Scan using _hyper_14_3092_chunk_metrics_values_labels_id_idx on _hyper_14_3092_chunk _mv_1 (cost=0.43..50.98 rows=13 width=12) (actual time=0.002..0.003 rows=1 loops=12,908)

  • Index Cond: ((labels_id = (min(l_1.id))) AND ("time" >= '2019-11-07 19:01:50.678+00'::timestamp with time zone) AND ("time" <= '2019-11-08 01:01:50.678+00'::timestamp with time zone))
  • Filter: (value = '2'::double precision)
32. 1,183.104 2,185.456 ↑ 1.0 200 8,216

Append (cost=0.42..5.59 rows=201 width=20) (actual time=0.009..0.266 rows=200 loops=8,216)

33. 205.400 205.400 ↑ 4.4 38 8,216

Index Scan using _hyper_14_3109_chunk_metrics_values_time_idx on _hyper_14_3109_chunk m (cost=0.42..4.23 rows=169 width=20) (actual time=0.003..0.025 rows=38 loops=8,216)

  • Index Cond: (("time" = _mv."time") AND ("time" >= '2019-11-07 19:01:50.678+00'::timestamp with time zone) AND ("time" <= '2019-11-08 01:01:50.678+00'::timestamp with time zone))
34. 796.952 796.952 ↓ 5.1 162 8,216

Index Scan using _hyper_14_3092_chunk_metrics_values_time_idx on _hyper_14_3092_chunk m_1 (cost=0.43..1.36 rows=32 width=20) (actual time=0.005..0.097 rows=162 loops=8,216)

  • Index Cond: (("time" = _mv."time") AND ("time" >= '2019-11-07 19:01:50.678+00'::timestamp with time zone) AND ("time" <= '2019-11-08 01:01:50.678+00'::timestamp with time zone))
35. 3,289.566 3,289.566 ↓ 0.0 0 1,644,783

Index Scan using metrics_labels_pkey on metrics_labels l (cost=0.42..0.49 rows=1 width=216) (actual time=0.002..0.002 rows=0 loops=1,644,783)

  • Index Cond: (id = m.labels_id)
  • Filter: (metric_name = 'version_id'::text)
  • Rows Removed by Filter: 1
36. 0.080 8,821.225 ↓ 148.0 148 1

Custom Scan (GapFill) (cost=2.63..2.64 rows=1 width=0) (actual time=8,821.136..8,821.225 rows=148 loops=1)

37. 0.047 8,821.145 ↓ 31.0 31 1

Sort (cost=2.63..2.64 rows=1 width=0) (actual time=8,821.132..8,821.145 rows=31 loops=1)

  • Sort Key: _qw.name, (time_bucket_gapfill('00:10:00'::interval, _qw."time"))
  • Sort Method: quicksort Memory: 29kB
38. 1.054 8,821.098 ↓ 31.0 31 1

GroupAggregate (cost=2.60..2.62 rows=1 width=0) (actual time=8,819.327..8,821.098 rows=31 loops=1)

  • Group Key: (time_bucket_gapfill('00:10:00'::interval, _qw."time")), _qw.name
39. 2.093 8,820.044 ↓ 2,111.0 2,111 1

Sort (cost=2.60..2.60 rows=1 width=48) (actual time=8,819.265..8,820.044 rows=2,111 loops=1)

  • Sort Key: (time_bucket_gapfill('00:10:00'::interval, _qw."time")), _qw.name
  • Sort Method: quicksort Memory: 393kB
40. 1.754 8,817.951 ↓ 2,111.0 2,111 1

Subquery Scan on _qw (cost=2.57..2.59 rows=1 width=48) (actual time=8,815.378..8,817.951 rows=2,111 loops=1)

41. 2.406 8,816.197 ↓ 2,111.0 2,111 1

Sort (cost=2.57..2.57 rows=1 width=48) (actual time=8,815.374..8,816.197 rows=2,111 loops=1)

  • Sort Key: q."time", (COALESCE(date_part('epoch'::text, (q."time" - (min(q.start_time)))), '0'::double precision))
  • Sort Method: quicksort Memory: 393kB
42. 6.837 8,813.791 ↓ 2,111.0 2,111 1

Nested Loop (cost=0.31..2.56 rows=1 width=48) (actual time=8,798.970..8,813.791 rows=2,111 loops=1)

43. 4.689 8,800.621 ↓ 2,111.0 2,111 1

HashAggregate (cost=0.03..0.04 rows=1 width=80) (actual time=8,798.946..8,800.621 rows=2,111 loops=1)

  • Group Key: q."time", q.cms_id, q.patch_id
  • Filter: (COALESCE(date_part('epoch'::text, (q."time" - min(q.start_time))), '0'::double precision) > '0'::double precision)
44. 8,795.932 8,795.932 ↓ 2,276.0 2,276 1

CTE Scan on q_patch_details q (cost=0.00..0.02 rows=1 width=80) (actual time=329.480..8,795.932 rows=2,276 loops=1)

45. 6.333 6.333 ↑ 1.0 1 2,111

Index Scan using game_meta_info_pkey on game_meta_info gmi (cost=0.28..2.49 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=2,111)

  • Index Cond: (cms_id = q.cms_id)
Planning time : 14.813 ms
Execution time : 8,823.354 ms