explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lo8q

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 14,448.802 ↑ 1.7 116 1

Finalize GroupAggregate (cost=443,328.29..444,033.87 rows=200 width=352) (actual time=14,443.515..14,448.802 rows=116 loops=1)

  • Output: cm.sub_region, cm.country_name, count(v_1.id), sum(v_1.view_count), sum(v_1.comment_count), sum(v_1.like_count), sum(v_1.dislike_count), (sum(v_1.comment_count) / NULLIF(sum(v_1.view_count), '0'::numeric)), ((sum(v_1.comment_count) + sum(v_1.like_count)) / NULLIF(sum(v_1.view_count), '0'::numeric)), (sum(v_1.like_count) / NULLIF(sum(v_1.dislike_count), '0'::numeric))
  • Group Key: cm.sub_region, cm.country_name
  • Buffers: shared hit=607100 read=83716 dirtied=446
  • I/O Timings: read=11568.786
2. 0.000 14,448.980 ↑ 1.8 446 1

Gather Merge (cost=443,328.29..443,972.87 rows=800 width=256) (actual time=14,443.468..14,448.980 rows=446 loops=1)

  • Output: cm.sub_region, cm.country_name, (PARTIAL count(v_1.id)), (PARTIAL sum(v_1.view_count)), (PARTIAL sum(v_1.comment_count)), (PARTIAL sum(v_1.like_count)), (PARTIAL sum(v_1.dislike_count))
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=3074027 read=420218 dirtied=2276
  • I/O Timings: read=57903.450
3. 14.905 72,181.260 ↑ 2.2 89 5

Partial GroupAggregate (cost=442,328.23..442,877.53 rows=200 width=256) (actual time=14,432.557..14,436.252 rows=89 loops=5)

  • Output: cm.sub_region, cm.country_name, PARTIAL count(v_1.id), PARTIAL sum(v_1.view_count), PARTIAL sum(v_1.comment_count), PARTIAL sum(v_1.like_count), PARTIAL sum(v_1.dislike_count)
  • Group Key: cm.sub_region, cm.country_name
  • Buffers: shared hit=3074027 read=420218 dirtied=2276
  • I/O Timings: read=57903.450
  • Worker 0: actual time=14429.639..14434.729 rows=91 loops=1
  • Buffers: shared hit=605031 read=82902 dirtied=453
  • I/O Timings: read=11570.134
  • Worker 1: actual time=14430.219..14433.085 rows=88 loops=1
  • Buffers: shared hit=612759 read=84370 dirtied=465
  • I/O Timings: read=11633.408
  • Worker 2: actual time=14434.107..14438.700 rows=88 loops=1
  • Buffers: shared hit=630223 read=85174 dirtied=457
  • I/O Timings: read=11539.485
  • Worker 3: actual time=14430.169..14433.025 rows=89 loops=1
  • Buffers: shared hit=618914 read=84056 dirtied=455
  • I/O Timings: read=11591.636
4. 63.930 72,166.355 ↑ 2.8 9,856 5

Sort (cost=442,328.23..442,396.39 rows=27,265 width=164) (actual time=14,432.497..14,433.271 rows=9,856 loops=5)

  • Output: cm.sub_region, cm.country_name, v_1.id, v_1.view_count, v_1.comment_count, v_1.like_count, v_1.dislike_count
  • Sort Key: cm.sub_region, cm.country_name
  • Sort Method: quicksort Memory: 1779kB
  • Worker 0: Sort Method: quicksort Memory: 1764kB
  • Worker 1: Sort Method: quicksort Memory: 1759kB
  • Worker 2: Sort Method: quicksort Memory: 1772kB
  • Worker 3: Sort Method: quicksort Memory: 1779kB
  • Buffers: shared hit=3074027 read=420218 dirtied=2276
  • I/O Timings: read=57903.450
  • Worker 0: actual time=14429.575..14430.556 rows=9812 loops=1
  • Buffers: shared hit=605031 read=82902 dirtied=453
  • I/O Timings: read=11570.134
  • Worker 1: actual time=14430.156..14430.778 rows=9775 loops=1
  • Buffers: shared hit=612759 read=84370 dirtied=465
  • I/O Timings: read=11633.408
  • Worker 2: actual time=14434.037..14434.984 rows=9869 loops=1
  • Buffers: shared hit=630223 read=85174 dirtied=457
  • I/O Timings: read=11539.485
  • Worker 3: actual time=14430.106..14430.703 rows=9913 loops=1
  • Buffers: shared hit=618914 read=84056 dirtied=455
  • I/O Timings: read=11591.636
5. 42.155 72,102.425 ↑ 2.8 9,856 5

Parallel Hash Join (cost=1,211.64..440,319.51 rows=27,265 width=164) (actual time=17.351..14,420.485 rows=9,856 loops=5)

  • Output: cm.sub_region, cm.country_name, v_1.id, v_1.view_count, v_1.comment_count, v_1.like_count, v_1.dislike_count
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=3073996 read=420217 dirtied=2276
  • I/O Timings: read=57902.977
  • Worker 0: actual time=11.180..14417.201 rows=9812 loops=1
  • Buffers: shared hit=605023 read=82902 dirtied=453
  • I/O Timings: read=11570.134
  • Worker 1: actual time=9.520..14417.889 rows=9775 loops=1
  • Buffers: shared hit=612751 read=84370 dirtied=465
  • I/O Timings: read=11633.408
  • Worker 2: actual time=36.755..14419.768 rows=9869 loops=1
  • Buffers: shared hit=630215 read=85174 dirtied=457
  • I/O Timings: read=11539.485
  • Worker 3: actual time=11.547..14417.949 rows=9913 loops=1
  • Buffers: shared hit=618907 read=84055 dirtied=455
  • I/O Timings: read=11591.163
6. 0.000 72,025.360 ↑ 2.8 9,856 5

Nested Loop (cost=16.40..439,052.69 rows=27,265 width=168) (actual time=10.189..14,405.072 rows=9,856 loops=5)

  • Output: v_1.id, v_1.view_count, v_1.comment_count, v_1.like_count, v_1.dislike_count, vg.game_id, cm.sub_region, cm.country_name
  • Inner Unique: true
  • Buffers: shared hit=3073943 read=419811 dirtied=2276
  • I/O Timings: read=57895.864
  • Worker 0: actual time=6.901..14404.652 rows=9812 loops=1
  • Buffers: shared hit=605011 read=82843 dirtied=453
  • I/O Timings: read=11569.943
  • Worker 1: actual time=3.761..14403.883 rows=9775 loops=1
  • Buffers: shared hit=612738 read=84303 dirtied=465
  • I/O Timings: read=11632.866
  • Worker 2: actual time=31.002..14405.681 rows=9869 loops=1
  • Buffers: shared hit=630202 read=85110 dirtied=457
  • I/O Timings: read=11539.086
  • Worker 3: actual time=5.773..14404.066 rows=9913 loops=1
  • Buffers: shared hit=618894 read=83971 dirtied=455
  • I/O Timings: read=11590.359
7. 153.895 38,510.360 ↓ 2.3 63,281 5

Hash Join (cost=15.83..396,042.97 rows=27,265 width=164) (actual time=1.585..7,702.072 rows=63,281 loops=5)

  • Output: v_1.id, v_1.view_count, v_1.comment_count, v_1.like_count, v_1.dislike_count, cm.sub_region, cm.country_name
  • Hash Cond: ((c.mat_country)::bpchar = cm.country_code_alpha2)
  • Buffers: shared hit=1891934 read=325269 dirtied=2276
  • I/O Timings: read=27661.441
  • Worker 0: actual time=1.496..7735.241 rows=62271 loops=1
  • Buffers: shared hit=372412 read=64215 dirtied=453
  • I/O Timings: read=5544.518
  • Worker 1: actual time=1.522..7709.054 rows=63061 loops=1
  • Buffers: shared hit=377230 read=65316 dirtied=465
  • I/O Timings: read=5587.650
  • Worker 2: actual time=1.789..7769.592 rows=64871 loops=1
  • Buffers: shared hit=387848 read=65874 dirtied=457
  • I/O Timings: read=5576.557
  • Worker 3: actual time=1.516..7636.978 rows=63713 loops=1
  • Buffers: shared hit=380649 read=65150 dirtied=455
  • I/O Timings: read=5486.769
8. 218.352 38,354.195 ↓ 1.1 65,952 5

Nested Loop (cost=1.11..394,070.84 rows=58,600 width=47) (actual time=1.065..7,670.839 rows=65,952 loops=5)

  • Output: v_1.id, v_1.view_count, v_1.comment_count, v_1.like_count, v_1.dislike_count, c.mat_country
  • Inner Unique: true
  • Buffers: shared hit=1891859 read=325264 dirtied=2276
  • I/O Timings: read=27660.702
  • Worker 0: actual time=0.544..7703.736 rows=64859 loops=1
  • Buffers: shared hit=372399 read=64210 dirtied=453
  • I/O Timings: read=5543.779
  • Worker 1: actual time=1.039..7678.031 rows=65793 loops=1
  • Buffers: shared hit=377212 read=65316 dirtied=465
  • I/O Timings: read=5587.650
  • Worker 2: actual time=1.615..7737.889 rows=67568 loops=1
  • Buffers: shared hit=387830 read=65874 dirtied=457
  • I/O Timings: read=5576.557
  • Worker 3: actual time=0.647..7605.422 rows=66351 loops=1
  • Buffers: shared hit=380631 read=65150 dirtied=455
  • I/O Timings: read=5486.769
9. 47.276 23,626.315 ↓ 1.1 65,952 5

Parallel Append (cost=0.56..243,419.72 rows=58,603 width=69) (actual time=0.764..4,725.263 rows=65,952 loops=5)

  • Buffers: shared hit=322323 read=245357 dirtied=273
  • I/O Timings: read=17461.038
  • Worker 0: actual time=0.500..4794.634 rows=64861 loops=1
  • Buffers: shared hit=63566 read=48585 dirtied=47
  • I/O Timings: read=3581.185
  • Worker 1: actual time=0.596..4659.201 rows=65793 loops=1
  • Buffers: shared hit=64138 read=49269 dirtied=58
  • I/O Timings: read=3440.987
  • Worker 2: actual time=1.233..4753.677 rows=67569 loops=1
  • Buffers: shared hit=65994 read=49702 dirtied=52
  • I/O Timings: read=3494.303
  • Worker 3: actual time=0.598..4711.968 rows=66351 loops=1
  • Buffers: shared hit=64776 read=49195 dirtied=69
  • I/O Timings: read=3508.679
10. 0.017 0.022 ↓ 0.0 0 2

Parallel Bitmap Heap Scan on youtube.videos_y2019_q4 v_1 (cost=1.24..11.40 rows=11 width=96) (actual time=0.011..0.011 rows=0 loops=2)

  • Output: v_1.id, v_1.view_count, v_1.comment_count, v_1.like_count, v_1.dislike_count, 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
  • Worker 1: actual time=0.004..0.004 rows=0 loops=1
  • Worker 2: actual time=0.019..0.019 rows=0 loops=1
  • Buffers: shared hit=1
11. 0.005 0.005 ↓ 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.005..0.005 rows=0 loops=1)

  • Index Cond: (v_1.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Buffers: shared hit=1
  • Worker 2: actual time=0.005..0.005 rows=0 loops=1
  • Buffers: shared hit=1
12. 0.039 0.042 ↓ 0.0 0 2

Parallel Bitmap Heap Scan on youtube.videos_y2020_q1 v_2 (cost=1.24..11.40 rows=11 width=96) (actual time=0.020..0.021 rows=0 loops=2)

  • Output: v_2.id, v_2.view_count, v_2.comment_count, v_2.like_count, v_2.dislike_count, 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
  • Worker 2: actual time=0.017..0.017 rows=0 loops=1
  • Worker 3: actual time=0.024..0.024 rows=0 loops=1
  • Buffers: shared hit=1
13. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Index Cond: (v_2.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Buffers: shared hit=1
  • Worker 3: actual time=0.003..0.003 rows=0 loops=1
  • Buffers: shared hit=1
14. 0.004 0.012 ↓ 0.0 0 1

Parallel Bitmap Heap Scan on youtube.videos_y2020_q2 v_3 (cost=1.24..11.40 rows=11 width=96) (actual time=0.011..0.012 rows=0 loops=1)

  • Output: v_3.id, v_3.view_count, v_3.comment_count, v_3.like_count, v_3.dislike_count, 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
  • Worker 1: actual time=0.011..0.012 rows=0 loops=1
  • Buffers: shared hit=1
15. 0.008 0.008 ↓ 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.008..0.008 rows=0 loops=1)

  • Index Cond: (v_3.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Buffers: shared hit=1
  • Worker 1: actual time=0.008..0.008 rows=0 loops=1
  • Buffers: shared hit=1
16. 23,578.960 23,578.960 ↓ 1.1 65,952 5

Parallel Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q3 v (cost=0.56..243,091.28 rows=58,588 width=69) (actual time=0.746..4,715.792 rows=65,952 loops=5)

  • Output: v.id, v.view_count, v.comment_count, v.like_count, v.dislike_count, v.channel_id
  • Index Cond: (v.published > '2019-09-01 00:00:00+00'::timestamp with time zone)
  • Heap Fetches: 186380
  • Buffers: shared hit=322319 read=245357 dirtied=273
  • I/O Timings: read=17461.038
  • Worker 0: actual time=0.499..4785.273 rows=64861 loops=1
  • Buffers: shared hit=63566 read=48585 dirtied=47
  • I/O Timings: read=3581.185
  • Worker 1: actual time=0.579..4649.744 rows=65793 loops=1
  • Buffers: shared hit=64137 read=49269 dirtied=58
  • I/O Timings: read=3440.987
  • Worker 2: actual time=1.196..4743.934 rows=67569 loops=1
  • Buffers: shared hit=65993 read=49702 dirtied=52
  • I/O Timings: read=3494.303
  • Worker 3: actual time=0.571..4702.542 rows=66351 loops=1
  • Buffers: shared hit=64775 read=49195 dirtied=69
  • I/O Timings: read=3508.679
17. 0.003 0.003 ↓ 0.0 0 1

Parallel 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=76) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: v_4.id, v_4.view_count, v_4.comment_count, v_4.like_count, v_4.dislike_count, 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
18. 14,509.528 14,509.528 ↑ 1.0 1 329,762

Index Scan using channels_pkey on youtube.channels c (cost=0.56..2.57 rows=1 width=28) (actual time=0.044..0.044 rows=1 loops=329,762)

  • Output: c.id, c.name, c.description, c.country, c.published, c.last_video_published, c.subscriber_count, c.video_count, c.comment_count, c.view_count, c.estimated_country, c.estimated_language, c.mat_country, c.avatar_url, c.topic_ids, c.is_deleted, c.last_synced, c.last_stats_synced, c.topic_categories
  • Index Cond: (c.id = v_1.channel_id)
  • Buffers: shared hit=1569536 read=79907 dirtied=2003
  • I/O Timings: read=10199.664
  • Worker 0: actual time=0.044..0.044 rows=1 loops=64861
  • Buffers: shared hit=308833 read=15625 dirtied=406
  • I/O Timings: read=1962.594
  • Worker 1: actual time=0.045..0.045 rows=1 loops=65793
  • Buffers: shared hit=313074 read=16047 dirtied=407
  • I/O Timings: read=2146.663
  • Worker 2: actual time=0.043..0.043 rows=1 loops=67569
  • Buffers: shared hit=321836 read=16172 dirtied=405
  • I/O Timings: read=2082.254
  • Worker 3: actual time=0.043..0.043 rows=1 loops=66351
  • Buffers: shared hit=315855 read=15955 dirtied=386
  • I/O Timings: read=1978.090
19. 0.320 2.270 ↓ 1.2 249 5

Hash (cost=12.10..12.10 rows=210 width=132) (actual time=0.453..0.454 rows=249 loops=5)

  • Output: cm.sub_region, cm.country_name, cm.country_code_alpha2
  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=20 read=5
  • I/O Timings: read=0.739
  • Worker 0: actual time=0.867..0.867 rows=249 loops=1
  • Buffers: shared read=5
  • I/O Timings: read=0.739
  • Worker 1: actual time=0.389..0.389 rows=249 loops=1
  • Buffers: shared hit=5
  • Worker 2: actual time=0.100..0.100 rows=249 loops=1
  • Buffers: shared hit=5
  • Worker 3: actual time=0.819..0.819 rows=249 loops=1
  • Buffers: shared hit=5
20. 1.950 1.950 ↓ 1.2 249 5

Seq Scan on meta.country_meta cm (cost=0.00..12.10 rows=210 width=132) (actual time=0.177..0.390 rows=249 loops=5)

  • Output: cm.sub_region, cm.country_name, cm.country_code_alpha2
  • Buffers: shared hit=20 read=5
  • I/O Timings: read=0.739
  • Worker 0: actual time=0.462..0.808 rows=249 loops=1
  • Buffers: shared read=5
  • I/O Timings: read=0.739
  • Worker 1: actual time=0.008..0.298 rows=249 loops=1
  • Buffers: shared hit=5
  • Worker 2: actual time=0.008..0.042 rows=249 loops=1
  • Buffers: shared hit=5
  • Worker 3: actual time=0.405..0.758 rows=249 loops=1
  • Buffers: shared hit=5
21. 33,538.930 33,538.930 ↓ 0.0 0 316,405

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.106..0.106 rows=0 loops=316,405)

  • Output: vg.youtube_video_id, vg.game_id
  • Index Cond: (vg.youtube_video_id = v_1.id)
  • Heap Fetches: 0
  • Buffers: shared hit=1182009 read=94542
  • I/O Timings: read=30234.423
  • Worker 0: actual time=0.107..0.107 rows=0 loops=62271
  • Buffers: shared hit=232599 read=18628
  • I/O Timings: read=6025.425
  • Worker 1: actual time=0.106..0.106 rows=0 loops=63061
  • Buffers: shared hit=235508 read=18987
  • I/O Timings: read=6045.216
  • Worker 2: actual time=0.102..0.102 rows=0 loops=64871
  • Buffers: shared hit=242354 read=19236
  • I/O Timings: read=5962.529
  • Worker 3: actual time=0.106..0.106 rows=0 loops=63713
  • Buffers: shared hit=238245 read=18821
  • I/O Timings: read=6103.590
22. 13.665 34.910 ↑ 2.1 10,184 5

Parallel Hash (cost=925.56..925.56 rows=21,574 width=4) (actual time=6.982..6.982 rows=10,184 loops=5)

  • Output: g.id
  • Buckets: 65536 Batches: 1 Memory Usage: 2528kB
  • Buffers: shared hit=9 read=406
  • I/O Timings: read=7.113
  • Worker 0: actual time=4.219..4.219 rows=7320 loops=1
  • Buffers: shared hit=1 read=59
  • I/O Timings: read=0.191
  • Worker 1: actual time=5.687..5.687 rows=8990 loops=1
  • Buffers: shared hit=2 read=67
  • I/O Timings: read=0.542
  • Worker 2: actual time=5.687..5.687 rows=8172 loops=1
  • Buffers: shared hit=2 read=64
  • I/O Timings: read=0.399
  • Worker 3: actual time=5.715..5.715 rows=10116 loops=1
  • Buffers: shared hit=2 read=84
  • I/O Timings: read=0.804
23. 21.245 21.245 ↑ 2.1 10,184 5

Parallel Index Only Scan using games_pkey on topic.games g (cost=0.29..925.56 rows=21,574 width=4) (actual time=0.109..4.249 rows=10,184 loops=5)

  • Output: g.id
  • Heap Fetches: 6
  • Buffers: shared hit=9 read=406
  • I/O Timings: read=7.113
  • Worker 0: actual time=0.026..2.268 rows=7320 loops=1
  • Buffers: shared hit=1 read=59
  • I/O Timings: read=0.191
  • Worker 1: actual time=0.033..3.125 rows=8990 loops=1
  • Buffers: shared hit=2 read=67
  • I/O Timings: read=0.542
  • Worker 2: actual time=0.021..3.225 rows=8172 loops=1
  • Buffers: shared hit=2 read=64
  • I/O Timings: read=0.399
  • Worker 3: actual time=0.031..3.300 rows=10116 loops=1
  • Buffers: shared hit=2 read=84
  • I/O Timings: read=0.804
Planning time : 3.618 ms