explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fPtU

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 5,984.165 ↑ 1.0 100 1

Limit (cost=1,136,868.98..1,136,869.23 rows=100 width=64) (actual time=5,984.143..5,984.165 rows=100 loops=1)

  • Output: ((((('<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)), (sum(v.view_count))
  • Buffers: shared hit=2234694, temp read=12032 written=12032
2. 1.027 5,984.149 ↑ 516.2 100 1

Sort (cost=1,136,868.98..1,136,998.03 rows=51,621 width=64) (actual time=5,984.142..5,984.149 rows=100 loops=1)

  • Output: ((((('<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)), (sum(v.view_count))
  • Sort Key: (sum(v.view_count)) DESC
  • Sort Method: top-N heapsort Memory: 70kB
  • Buffers: shared hit=2234694, temp read=12032 written=12032
3. 22.843 5,983.122 ↑ 22.4 2,304 1

HashAggregate (cost=1,133,734.59..1,134,896.06 rows=51,621 width=64) (actual time=5,981.809..5,983.122 rows=2,304 loops=1)

  • Output: ((((('<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)), sum(v.view_count)
  • 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)
  • Buffers: shared hit=2234694, temp read=12032 written=12032
4. 26.636 5,960.279 ↑ 4.6 50,596 1

Hash Join (cost=482,781.18..1,132,565.11 rows=233,896 width=40) (actual time=2,291.011..5,960.279 rows=50,596 loops=1)

  • Output: (((('<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), v.view_count
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=2234694, temp read=12032 written=12032
5. 0.000 5,901.278 ↑ 4.6 50,596 1

Nested Loop (cost=461,496.18..1,108,327.11 rows=233,896 width=12) (actual time=2,258.581..5,901.278 rows=50,596 loops=1)

  • Output: v.view_count, vg.game_id
  • Inner Unique: true
  • Buffers: shared hit=2214574, temp read=12032 written=12032
6. 476.081 3,285.696 ↓ 1.4 329,066 1

Hash Join (cost=461,495.61..739,363.13 rows=233,896 width=20) (actual time=2,258.562..3,285.696 rows=329,066 loops=1)

  • Output: v.view_count, v.id
  • Inner Unique: true
  • Hash Cond: (v.channel_id = c.id)
  • Buffers: shared hit=887050, temp read=12032 written=12032
7. 28.472 554.646 ↓ 1.4 329,191 1

Append (cost=0.56..246,056.75 rows=234,409 width=45) (actual time=0.015..554.646 rows=329,191 loops=1)

  • Buffers: shared hit=561669
8. 526.118 526.118 ↓ 1.4 329,191 1

Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q3 v (cost=0.56..244,848.94 rows=234,354 width=45) (actual time=0.015..526.118 rows=329,191 loops=1)

  • Output: v.view_count, v.id, v.channel_id
  • Index Cond: (v.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Heap Fetches: 181756
  • Buffers: shared hit=561665
9. 0.002 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on youtube.videos_y2019_q4 v_1 (cost=1.24..11.51 rows=18 width=72) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: v_1.view_count, v_1.id, v_1.channel_id
  • Recheck Cond: ((v_1.published > '2019-09-01 00:00:00+00'::timestamp with time zone) AND (v_1.view_count > 1000))
  • Buffers: shared hit=1
10. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on videos_y2019_q4_channel_id_published_id_view_count_comment__idx (cost=0.00..1.23 rows=18 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (v_1.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Buffers: shared hit=1
11. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on youtube.videos_y2020_q1 v_2 (cost=1.24..11.51 rows=18 width=72) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: v_2.view_count, v_2.id, v_2.channel_id
  • Recheck Cond: ((v_2.published > '2019-09-01 00:00:00+00'::timestamp with time zone) AND (v_2.view_count > 1000))
  • Buffers: shared hit=1
12. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on videos_y2020_q1_channel_id_published_id_view_count_comment__idx (cost=0.00..1.23 rows=18 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (v_2.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Buffers: shared hit=1
13. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on youtube.videos_y2020_q2 v_3 (cost=1.24..11.51 rows=18 width=72) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: v_3.view_count, v_3.id, v_3.channel_id
  • Recheck Cond: ((v_3.published > '2019-09-01 00:00:00+00'::timestamp with time zone) AND (v_3.view_count > 1000))
  • Buffers: shared hit=1
14. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on videos_y2020_q2_channel_id_published_id_view_count_comment__idx (cost=0.00..1.23 rows=18 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (v_3.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Buffers: shared hit=1
15. 0.048 0.048 ↓ 0.0 0 1

Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on youtube.videos_default v_4 (cost=0.12..1.24 rows=1 width=52) (actual time=0.048..0.048 rows=0 loops=1)

  • Output: v_4.view_count, v_4.id, v_4.channel_id
  • Index Cond: (v_4.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Heap Fetches: 0
  • Buffers: shared hit=1
16. 971.619 2,254.969 ↓ 1.0 3,962,635 1

Hash (cost=384,915.31..384,915.31 rows=3,960,460 width=25) (actual time=2,254.969..2,254.969 rows=3,962,635 loops=1)

  • Output: c.id
  • Buckets: 4194304 Batches: 2 Memory Usage: 143138kB
  • Buffers: shared hit=325381, temp written=10878
17. 1,283.350 1,283.350 ↓ 1.0 3,962,635 1

Seq Scan on youtube.channels c (cost=0.00..384,915.31 rows=3,960,460 width=25) (actual time=0.013..1,283.350 rows=3,962,635 loops=1)

  • Output: c.id
  • Filter: ((c.subscriber_count >= 0) AND (c.subscriber_count <= '99999999999'::bigint))
  • Rows Removed by Filter: 7705
  • Buffers: shared hit=325381
18. 2,632.528 2,632.528 ↓ 0.0 0 329,066

Index Only Scan using youtube_video_to_game_2_pkey on matching.youtube_video_to_game_2 vg (cost=0.56..1.58 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=329,066)

  • Output: vg.youtube_video_id, vg.game_id
  • Index Cond: (vg.youtube_video_id = v.id)
  • Heap Fetches: 0
  • Buffers: shared hit=1327524
19. 9.526 32.365 ↑ 1.0 50,922 1

Hash (cost=20,637.78..20,637.78 rows=51,778 width=29) (actual time=32.365..32.365 rows=50,922 loops=1)

  • Output: g.title, g.id
  • Buckets: 65536 Batches: 1 Memory Usage: 3654kB
  • Buffers: shared hit=20120
20. 22.839 22.839 ↑ 1.0 50,922 1

Seq Scan on topic.games g (cost=0.00..20,637.78 rows=51,778 width=29) (actual time=0.006..22.839 rows=50,922 loops=1)

  • Output: g.title, g.id
  • Buffers: shared hit=20120
Planning time : 3.269 ms