explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QcSH

Settings
# exclusive inclusive rows x rows loops node
1. 15.699 1,066.803 ↑ 4.3 13 1

Merge Join (cost=135,639.66..1,275,929,724.08 rows=56 width=240) (actual time=1,051.084..1,066.803 rows=13 loops=1)

  • Merge Cond: (yt_video_values.id = _hyper_4_484_chunk_1.yt_video_id)
2.          

CTE yt_video_values

3. 0.005 0.005 ↑ 1.0 13 1

Values Scan on "*VALUES*" (cost=0.00..0.16 rows=13 width=36) (actual time=0.002..0.005 rows=13 loops=1)

4. 0.012 1,049.739 ↑ 1.0 13 1

Merge Left Join (cost=67,852.86..1,275,861,699.78 rows=13 width=76) (actual time=1,048.836..1,049.739 rows=13 loops=1)

  • Merge Cond: (yt_video_values.id = _hyper_8_519_chunk.yt_video_id)
5. 0.021 2.019 ↑ 1.0 13 1

Merge Join (cost=67,850.96..67,877.78 rows=13 width=72) (actual time=1.553..2.019 rows=13 loops=1)

  • Merge Cond: (yt_video_values.id = _hyper_4_484_chunk.yt_video_id)
6. 0.007 0.019 ↑ 1.0 13 1

Sort (cost=0.50..0.53 rows=13 width=36) (actual time=0.016..0.019 rows=13 loops=1)

  • Sort Key: yt_video_values.id
  • Sort Method: quicksort Memory: 26kB
7. 0.012 0.012 ↑ 1.0 13 1

CTE Scan on yt_video_values (cost=0.00..0.26 rows=13 width=36) (actual time=0.004..0.012 rows=13 loops=1)

8. 0.354 1.979 ↑ 15.4 13 1

GroupAggregate (cost=67,850.46..67,874.59 rows=200 width=36) (actual time=1.534..1.979 rows=13 loops=1)

  • Group Key: _hyper_4_484_chunk.yt_video_id
9. 0.085 1.625 ↑ 6.7 130 1

Unique (cost=67,850.46..67,856.95 rows=865 width=32) (actual time=1.488..1.625 rows=130 loops=1)

10. 0.224 1.540 ↑ 1.1 806 1

Sort (cost=67,850.46..67,852.62 rows=865 width=32) (actual time=1.487..1.540 rows=806 loops=1)

  • Sort Key: _hyper_4_484_chunk.yt_video_id, (ntile(10) OVER (?))
  • Sort Method: quicksort Memory: 87kB
11. 0.394 1.316 ↑ 1.1 806 1

WindowAgg (cost=67,786.64..67,808.26 rows=865 width=32) (actual time=0.910..1.316 rows=806 loops=1)

12. 0.319 0.922 ↑ 1.1 806 1

Sort (cost=67,786.64..67,788.80 rows=865 width=20) (actual time=0.887..0.922 rows=806 loops=1)

  • Sort Key: _hyper_4_484_chunk.yt_video_id, _hyper_4_484_chunk.created_at DESC
  • Sort Method: quicksort Memory: 87kB
13. 0.073 0.603 ↑ 1.1 806 1

Nested Loop (cost=0.87..67,744.44 rows=865 width=20) (actual time=0.029..0.603 rows=806 loops=1)

14. 0.008 0.010 ↑ 1.0 13 1

HashAggregate (cost=0.29..0.42 rows=13 width=4) (actual time=0.007..0.010 rows=13 loops=1)

  • Group Key: yt_video_values_1.id
15. 0.002 0.002 ↑ 1.0 13 1

CTE Scan on yt_video_values yt_video_values_1 (cost=0.00..0.26 rows=13 width=4) (actual time=0.000..0.002 rows=13 loops=1)

16. 0.065 0.520 ↑ 174.9 62 13

Append (cost=0.58..5,102.67 rows=10,841 width=20) (actual time=0.006..0.040 rows=62 loops=13)

17. 0.104 0.104 ↑ 279.3 9 13

Index Only Scan using _hyper_4_484_chunk_index_yt_video_view_counts_on_yt_video_id_cr on _hyper_4_484_chunk (cost=0.58..1,171.38 rows=2,514 width=20) (actual time=0.006..0.008 rows=9 loops=13)

  • Index Cond: ((yt_video_id = yt_video_values_1.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 117
18. 0.156 0.156 ↑ 276.7 30 13

Index Only Scan using _hyper_4_205_chunk_index_yt_video_view_counts_on_yt_video_id_cr on _hyper_4_205_chunk (cost=0.58..3,850.03 rows=8,302 width=20) (actual time=0.006..0.012 rows=30 loops=13)

  • Index Cond: ((yt_video_id = yt_video_values_1.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 390
19. 0.195 0.195 ↑ 1.1 23 13

Index Only Scan using _hyper_4_510_chunk_index_yt_video_view_counts_on_yt_video_id_cr on _hyper_4_510_chunk (cost=0.58..27.06 rows=25 width=20) (actual time=0.006..0.015 rows=23 loops=13)

  • Index Cond: ((yt_video_id = yt_video_values_1.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 299
20. 0.017 1,047.708 ↑ 15.4 13 1

Unique (cost=1.89..1,275,793,819.34 rows=200 width=16) (actual time=1,047.281..1,047.708 rows=13 loops=1)

21. 47.767 1,047.691 ↑ 183,141,653.1 157 1

Merge Semi Join (cost=1.89..1,203,910,720.50 rows=28,753,239,537 width=16) (actual time=1,047.279..1,047.691 rows=157 loops=1)

  • Merge Cond: (_hyper_8_519_chunk.yt_video_id = yt_video_values_2.id)
22. 101.009 999.907 ↑ 69,663.4 825,490 1

Merge Append (cost=1.39..1,022,765,310.85 rows=57,506,479,074 width=16) (actual time=0.214..999.907 rows=825,490 loops=1)

  • Sort Key: _hyper_8_519_chunk.yt_video_id, _hyper_8_519_chunk.created_at DESC
23. 5.108 27.956 ↑ 2,283.7 11,127 1

Custom Scan (DecompressChunk) on _hyper_8_519_chunk (cost=0.43..10,835.90 rows=25,411,000 width=16) (actual time=0.055..27.956 rows=11,127 loops=1)

  • Filter: (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone)
24. 22.848 22.848 ↑ 2.3 11,127 1

Index Scan using compress_hyper_11_534_chunk__compressed_hypertable_11_yt_video_ on compress_hyper_11_534_chunk (cost=0.29..10,835.90 rows=25,411 width=92) (actual time=0.052..22.848 rows=11,127 loops=1)

  • Filter: (_ts_meta_max_1 >= '2020-05-28 15:18:40.932776'::timestamp without time zone)
25. 345.008 345.008 ↑ 734.0 495,473 1

Index Scan using _hyper_8_533_chunk_index_yt_video_growth_metrics_on_yt_video_id on _hyper_8_533_chunk (cost=0.57..258,270,803.08 rows=363,664,074 width=16) (actual time=0.038..345.008 rows=495,473 loops=1)

  • Index Cond: (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone)
26. 451.067 525.934 ↑ 179,112.1 318,892 1

Custom Scan (DecompressChunk) on _hyper_8_524_chunk (cost=0.37..21,223,212.07 rows=57,117,404,000 width=16) (actual time=0.119..525.934 rows=318,892 loops=1)

  • Filter: (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone)
27. 74.867 74.867 ↑ 831.9 68,659 1

Index Scan using compress_hyper_11_540_chunk__compressed_hypertable_11_yt_video_ on compress_hyper_11_540_chunk (cost=0.56..21,223,212.07 rows=57,117,404 width=92) (actual time=0.089..74.867 rows=68,659 loops=1)

  • Filter: (_ts_meta_max_1 >= '2020-05-28 15:18:40.932776'::timestamp without time zone)
28. 0.015 0.017 ↑ 1.0 13 1

Sort (cost=0.50..0.53 rows=13 width=4) (actual time=0.013..0.017 rows=13 loops=1)

  • Sort Key: yt_video_values_2.id
  • Sort Method: quicksort Memory: 25kB
29. 0.002 0.002 ↑ 1.0 13 1

CTE Scan on yt_video_values yt_video_values_2 (cost=0.00..0.26 rows=13 width=4) (actual time=0.001..0.002 rows=13 loops=1)

30. 0.624 1.365 ↑ 66.5 13 1

GroupAggregate (cost=67,786.64..67,810.43 rows=865 width=68) (actual time=0.768..1.365 rows=13 loops=1)

  • Group Key: _hyper_4_484_chunk_1.yt_video_id
31. 0.209 0.741 ↑ 1.1 806 1

Sort (cost=67,786.64..67,788.80 rows=865 width=20) (actual time=0.697..0.741 rows=806 loops=1)

  • Sort Key: _hyper_4_484_chunk_1.yt_video_id
  • Sort Method: quicksort Memory: 87kB
32. 0.079 0.532 ↑ 1.1 806 1

Nested Loop (cost=0.87..67,744.44 rows=865 width=20) (actual time=0.036..0.532 rows=806 loops=1)

33. 0.008 0.011 ↑ 1.0 13 1

HashAggregate (cost=0.29..0.42 rows=13 width=4) (actual time=0.008..0.011 rows=13 loops=1)

  • Group Key: yt_video_values_3.id
34. 0.003 0.003 ↑ 1.0 13 1

CTE Scan on yt_video_values yt_video_values_3 (cost=0.00..0.26 rows=13 width=4) (actual time=0.001..0.003 rows=13 loops=1)

35. 0.065 0.442 ↑ 174.9 62 13

Append (cost=0.58..5,102.67 rows=10,841 width=20) (actual time=0.005..0.034 rows=62 loops=13)

36. 0.078 0.078 ↑ 279.3 9 13

Index Only Scan using _hyper_4_484_chunk_index_yt_video_view_counts_on_yt_video_id_cr on _hyper_4_484_chunk _hyper_4_484_chunk_1 (cost=0.58..1,171.38 rows=2,514 width=20) (actual time=0.005..0.006 rows=9 loops=13)

  • Index Cond: ((yt_video_id = yt_video_values_3.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 117
37. 0.130 0.130 ↑ 276.7 30 13

Index Only Scan using _hyper_4_205_chunk_index_yt_video_view_counts_on_yt_video_id_cr on _hyper_4_205_chunk _hyper_4_205_chunk_1 (cost=0.58..3,850.03 rows=8,302 width=20) (actual time=0.004..0.010 rows=30 loops=13)

  • Index Cond: ((yt_video_id = yt_video_values_3.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 390
38. 0.169 0.169 ↑ 1.1 23 13

Index Only Scan using _hyper_4_510_chunk_index_yt_video_view_counts_on_yt_video_id_cr on _hyper_4_510_chunk _hyper_4_510_chunk_1 (cost=0.58..27.06 rows=25 width=20) (actual time=0.004..0.013 rows=23 loops=13)

  • Index Cond: ((yt_video_id = yt_video_values_3.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 299
Planning time : 3.158 ms
Execution time : 1,067.427 ms