explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4jO

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 29,913.539 ↑ 1.0 100 1

Limit (cost=1,351,500.21..1,351,500.46 rows=100 width=128) (actual time=29,913.516..29,913.539 rows=100 loops=1)

2. 1.962 29,913.522 ↑ 515.4 100 1

Sort (cost=1,351,500.21..1,351,629.07 rows=51,542 width=128) (actual time=29,913.514..29,913.522 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. 251.118 29,911.560 ↑ 8.2 6,268 1

HashAggregate (cost=1,347,468.63..1,349,530.31 rows=51,542 width=128) (actual time=29,906.459..29,911.560 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. 265.591 29,660.442 ↑ 4.2 603,193 1

Hash Join (cost=699,957.48..1,245,455.63 rows=2,550,325 width=48) (actual time=19,729.704..29,660.442 rows=603,193 loops=1)

  • Hash Cond: (vg.game_id = g.id)
5. 749.026 29,285.965 ↑ 4.2 603,193 1

Hash Join (cost=678,674.26..1,191,974.01 rows=2,550,325 width=20) (actual time=19,620.756..29,285.965 rows=603,193 loops=1)

  • Hash Cond: (v.channel_id = c.id)
6. 6,479.915 26,127.004 ↑ 4.2 603,193 1

Hash Join (cost=236,989.31..671,658.13 rows=2,550,444 width=45) (actual time=17,206.820..26,127.004 rows=603,193 loops=1)

  • Hash Cond: (vg.youtube_video_id = v.id)
7. 2,461.060 2,461.060 ↓ 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..2,461.060 rows=17,201,317 loops=1)

8. 1,480.154 17,186.029 ↓ 1.3 3,264,087 1

Hash (cost=205,108.76..205,108.76 rows=2,550,444 width=53) (actual time=17,186.028..17,186.029 rows=3,264,087 loops=1)

  • Buckets: 4194304 (originally 4194304) Batches: 2 (originally 1) Memory Usage: 229377kB
9. 263.216 15,705.875 ↓ 1.3 3,264,087 1

Append (cost=0.56..205,108.76 rows=2,550,444 width=53) (actual time=0.033..15,705.875 rows=3,264,087 loops=1)

10. 15,442.655 15,442.655 ↓ 1.3 3,264,087 1

Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v (cost=0.56..192,355.29 rows=2,550,443 width=53) (actual time=0.031..15,442.655 rows=3,264,087 loops=1)

  • Filter: (((published > '2019-08-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-09-01 00:00:00+00'::timestamp with time zone)) OR ((published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-08-01 00:00:00+00'::timestamp with time zone)))
  • Rows Removed by Filter: 197498
  • Heap Fetches: 877336
11. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

  • Filter: (((published > '2019-08-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-09-01 00:00:00+00'::timestamp with time zone)) OR ((published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-08-01 00:00:00+00'::timestamp with time zone)))
  • Heap Fetches: 0
12. 940.659 2,409.935 ↓ 1.0 3,965,090 1

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

  • Buckets: 4194304 Batches: 2 Memory Usage: 143211kB
13. 1,469.276 1,469.276 ↓ 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=0.008..1,469.276 rows=3,965,090 loops=1)

14. 11.276 108.886 ↑ 1.0 50,841 1

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

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

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

Planning time : 3.423 ms