explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nXBR

Settings
# exclusive inclusive rows x rows loops node
1. 24.577 37.198 ↓ 13.0 13 1

Merge Left Join (cost=394.07..412.21 rows=1 width=245) (actual time=11.289..37.198 rows=13 loops=1)

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

CTE yt_video_values

3. 0.013 0.013 ↑ 1.0 13 1

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

4. 0.089 9.651 ↓ 13.0 13 1

Nested Loop (cost=220.80..221.32 rows=1 width=132) (actual time=4.016..9.651 rows=13 loops=1)

  • Join Filter: (yt_video_values.id = yt_video_view_counts_1.yt_video_id)
  • Rows Removed by Join Filter: 78
5. 0.117 3.881 ↓ 13.0 13 1

Nested Loop (cost=110.38..110.84 rows=1 width=104) (actual time=3.062..3.881 rows=13 loops=1)

  • Join Filter: (yt_video_view_counts.yt_video_id = yt_video_values.id)
  • Rows Removed by Join Filter: 156
6. 3.158 3.712 ↓ 13.0 13 1

GroupAggregate (cost=110.38..110.41 rows=1 width=68) (actual time=3.051..3.712 rows=13 loops=1)

  • Group Key: yt_video_view_counts.yt_video_id
7. 0.146 0.554 ↓ 208.0 208 1

Sort (cost=110.38..110.38 rows=1 width=20) (actual time=0.522..0.554 rows=208 loops=1)

  • Sort Key: yt_video_view_counts.yt_video_id
  • Sort Method: quicksort Memory: 41kB
8. 0.041 0.408 ↓ 208.0 208 1

Nested Loop (cost=0.72..110.37 rows=1 width=20) (actual time=0.086..0.408 rows=208 loops=1)

9. 0.019 0.042 ↑ 1.0 13 1

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

  • Group Key: yt_video_values_1.id
10. 0.023 0.023 ↑ 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.007..0.023 rows=13 loops=1)

11. 0.325 0.325 ↓ 16.0 16 13

Index Scan using index_yt_video_view_counts_on_yt_video_id_created_at_desc on yt_video_view_counts (cost=0.43..8.45 rows=1 width=20) (actual time=0.011..0.025 rows=16 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))
12. 0.052 0.052 ↑ 1.0 13 13

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

13. 4.303 5.681 ↓ 7.0 7 13

GroupAggregate (cost=110.42..110.46 rows=1 width=36) (actual time=0.132..0.437 rows=7 loops=13)

  • Group Key: yt_video_view_counts_1.yt_video_id
14. 0.377 1.378 ↓ 71.0 71 13

Unique (cost=110.42..110.43 rows=1 width=32) (actual time=0.065..0.106 rows=71 loops=13)

15. 0.261 1.001 ↓ 113.0 113 13

Sort (cost=110.42..110.42 rows=1 width=32) (actual time=0.065..0.077 rows=113 loops=13)

  • Sort Key: yt_video_view_counts_1.yt_video_id, (ntile(10) OVER (?))
  • Sort Method: quicksort Memory: 41kB
16. 0.250 0.740 ↓ 208.0 208 1

WindowAgg (cost=110.38..110.41 rows=1 width=32) (actual time=0.510..0.740 rows=208 loops=1)

17. 0.181 0.490 ↓ 208.0 208 1

Sort (cost=110.38..110.38 rows=1 width=20) (actual time=0.470..0.490 rows=208 loops=1)

  • Sort Key: yt_video_view_counts_1.yt_video_id, yt_video_view_counts_1.created_at DESC
  • Sort Method: quicksort Memory: 41kB
18. 0.052 0.309 ↓ 208.0 208 1

Nested Loop (cost=0.72..110.37 rows=1 width=20) (actual time=0.048..0.309 rows=208 loops=1)

19. 0.016 0.023 ↑ 1.0 13 1

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

  • Group Key: yt_video_values_2.id
20. 0.007 0.007 ↑ 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.007 rows=13 loops=1)

21. 0.234 0.234 ↓ 16.0 16 13

Index Scan using index_yt_video_view_counts_on_yt_video_id_created_at_desc on yt_video_view_counts yt_video_view_counts_1 (cost=0.43..8.45 rows=1 width=20) (actual time=0.007..0.018 rows=16 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))
22. 0.151 2.970 ↑ 2.1 177 1

Unique (cost=173.11..182.37 rows=380 width=21) (actual time=2.740..2.970 rows=177 loops=1)

23. 1.666 2.819 ↑ 1.9 957 1

Sort (cost=173.11..177.74 rows=1,853 width=21) (actual time=2.736..2.819 rows=957 loops=1)

  • Sort Key: yt_video_growth_metrics.yt_video_id, yt_video_growth_metrics.created_at DESC
  • Sort Method: quicksort Memory: 131kB
24. 1.153 1.153 ↑ 1.1 1,760 1

Seq Scan on yt_video_growth_metrics (cost=0.00..72.53 rows=1,853 width=21) (actual time=0.022..1.153 rows=1,760 loops=1)

Planning time : 4.541 ms
Execution time : 40.712 ms