explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZEl

Settings
# exclusive inclusive rows x rows loops node
1. 17.629 4,563.247 ↑ 4.3 13 1

Merge Join (cost=135,639.15..1,164,600,027.57 rows=56 width=240) (actual time=4,546.894..4,563.247 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.006 0.006 ↑ 1.0 13 1

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

4. 12.936 4,544.170 ↑ 1.0 13 1

Merge Left Join (cost=67,852.35..1,164,532,003.28 rows=13 width=76) (actual time=4,542.879..4,544.170 rows=13 loops=1)

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

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

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

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

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

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

8. 0.362 9.940 ↑ 15.4 13 1

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

  • Group Key: _hyper_4_484_chunk.yt_video_id
9. 0.088 9.578 ↑ 6.7 130 1

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

10. 0.231 9.490 ↑ 1.1 806 1

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

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

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

12. 0.366 8.824 ↑ 1.1 806 1

Sort (cost=67,786.64..67,788.80 rows=865 width=20) (actual time=8.786..8.824 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.088 8.458 ↑ 1.1 806 1

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

14. 0.010 0.011 ↑ 1.0 13 1

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

  • Group Key: yt_video_values_1.id
15. 0.001 0.001 ↑ 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.001 rows=13 loops=1)

16. 0.065 8.359 ↑ 174.9 62 13

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

17. 1.547 1.547 ↑ 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.081..0.119 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. 1.716 1.716 ↑ 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.128..0.132 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. 5.031 5.031 ↑ 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.069..0.387 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. 80.076 4,521.239 ↓ 343.4 68,672 1

Unique (cost=1.39..1,164,464,122.83 rows=200 width=16) (actual time=2.441..4,521.239 rows=68,672 loops=1)

21. 121.868 4,441.163 ↑ 69,662.9 825,490 1

Merge Append (cost=1.39..1,020,699,107.41 rows=57,506,006,170 width=16) (actual time=2.440..4,441.163 rows=825,490 loops=1)

  • Sort Key: _hyper_8_519_chunk.yt_video_id, _hyper_8_519_chunk.created_at DESC
22. 5.439 49.337 ↑ 2,283.7 11,127 1

Custom Scan (DecompressChunk) on _hyper_8_519_chunk (cost=0.42..10,764.05 rows=25,411,000 width=16) (actual time=0.087..49.337 rows=11,127 loops=1)

23. 43.898 43.898 ↑ 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,764.05 rows=25,411 width=92) (actual time=0.078..43.898 rows=11,127 loops=1)

24. 804.006 943.681 ↑ 179,113.6 318,892 1

Custom Scan (DecompressChunk) on _hyper_8_524_chunk (cost=0.37..21,059,113.69 rows=57,117,904,000 width=16) (actual time=1.652..943.681 rows=318,892 loops=1)

25. 139.675 139.675 ↑ 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,059,113.69 rows=57,117,904 width=92) (actual time=0.474..139.675 rows=68,659 loops=1)

26. 3,326.277 3,326.277 ↑ 732.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..256,374,882.07 rows=362,691,170 width=16) (actual time=0.698..3,326.277 rows=495,473 loops=1)

27. 0.651 1.448 ↑ 66.5 13 1

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

  • Group Key: _hyper_4_484_chunk_1.yt_video_id
28. 0.247 0.797 ↑ 1.1 806 1

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

  • Sort Key: _hyper_4_484_chunk_1.yt_video_id
  • Sort Method: quicksort Memory: 87kB
29. 0.071 0.550 ↑ 1.1 806 1

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

30. 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_2.id
31. 0.003 0.003 ↑ 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.003 rows=13 loops=1)

32. 0.065 0.468 ↑ 174.9 62 13

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

33. 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_2.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 117
34. 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.005..0.010 rows=30 loops=13)

  • Index Cond: ((yt_video_id = yt_video_values_2.id) AND (created_at >= '2020-05-28 15:18:40.932776'::timestamp without time zone))
  • Heap Fetches: 390
35. 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 _hyper_4_510_chunk_1 (cost=0.58..27.06 rows=25 width=20) (actual time=0.005..0.015 rows=23 loops=13)

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