explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wRP0

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 49,585.040 ↑ 1.0 100 1

Limit (cost=1,425,022.65..1,425,022.90 rows=100 width=128) (actual time=49,585.019..49,585.040 rows=100 loops=1)

2. 2.019 49,585.025 ↑ 515.4 100 1

Sort (cost=1,425,022.65..1,425,151.51 rows=51,542 width=128) (actual time=49,585.018..49,585.025 rows=100 loops=1)

  • Sort Key: (((sum(v.view_count) FILTER (WHERE ((v.published > '2019-08-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-09-01 00:00:00+00'::timestamp with time zone))) - sum(v.view_count) FILTER (WHERE ((v.published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-08-01 00:00:00+00'::timestamp with time zone)))) / NULLIF(sum(v.view_count) FILTER (WHERE ((v.published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-08-01 00:00:00+00'::timestamp with time zone))), '0'::numeric))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 65kB
3. 255.203 49,583.006 ↑ 8.2 6,268 1

HashAggregate (cost=1,420,991.07..1,423,052.75 rows=51,542 width=128) (actual time=49,577.848..49,583.006 rows=6,268 loops=1)

  • Group Key: (((('<a href=""https://app.cluvio.com/dashboards/z926-3wgx-npdr?filters=%7B%22game_title%22%3A%5B%22'::text || g.title) || '%22%5D%7D"">'::text) || g.title) || '</a>'::text)
4. 259.454 49,327.803 ↑ 5.5 603,203 1

Hash Join (cost=805,174.25..1,288,997.95 rows=3,299,828 width=48) (actual time=42,176.751..49,327.803 rows=603,203 loops=1)

  • Hash Cond: (vg.game_id = g.id)
5. 5,692.537 48,698.267 ↑ 5.5 603,203 1

Hash Join (cost=783,891.02..1,226,053.69 rows=3,299,828 width=20) (actual time=41,806.604..48,698.267 rows=603,203 loops=1)

  • Hash Cond: (vg.youtube_video_id = v.id)
6. 1,219.401 1,219.401 ↓ 1.0 17,201,317 1

Seq Scan on youtube_video_to_game_2 vg (cost=0.00..301,656.16 rows=17,201,316 width=16) (actual time=0.005..1,219.401 rows=17,201,317 loops=1)

7. 1,079.703 41,786.329 ↑ 1.0 3,264,356 1

Hash (cost=742,643.17..742,643.17 rows=3,299,828 width=28) (actual time=41,786.329..41,786.329 rows=3,264,356 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 224039kB
8. 1,568.343 40,706.626 ↑ 1.0 3,264,356 1

Hash Join (cost=441,685.50..742,643.17 rows=3,299,828 width=28) (actual time=7,437.108..40,706.626 rows=3,264,356 loops=1)

  • Hash Cond: (v.channel_id = c.id)
9. 271.495 31,722.739 ↑ 1.0 3,264,376 1

Append (cost=0.56..200,739.76 rows=3,299,982 width=53) (actual time=0.578..31,722.739 rows=3,264,376 loops=1)

10. 31,451.237 31,451.237 ↑ 1.0 3,264,376 1

Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v (cost=0.56..184,238.60 rows=3,299,981 width=53) (actual time=0.577..31,451.237 rows=3,264,376 loops=1)

  • Index Cond: ((published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 156474
11. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on videos_default v_1 (cost=0.12..1.25 rows=1 width=60) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
12. 1,193.078 7,415.544 ↓ 1.0 3,965,090 1

Hash (cost=365,026.31..365,026.31 rows=3,964,531 width=25) (actual time=7,415.544..7,415.544 rows=3,965,090 loops=1)

  • Buckets: 4194304 Batches: 2 Memory Usage: 143211kB
13. 6,222.466 6,222.466 ↓ 1.0 3,965,090 1

Seq Scan on channels c (cost=0.00..365,026.31 rows=3,964,531 width=25) (actual time=1.204..6,222.466 rows=3,965,090 loops=1)

14. 13.717 370.082 ↑ 1.0 50,841 1

Hash (cost=20,636.99..20,636.99 rows=51,699 width=29) (actual time=370.082..370.082 rows=50,841 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3649kB
15. 356.365 356.365 ↑ 1.0 50,841 1

Seq Scan on games g (cost=0.00..20,636.99 rows=51,699 width=29) (actual time=0.573..356.365 rows=50,841 loops=1)

Planning time : 3.284 ms