explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bvMb

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 6,905.950 ↑ 325.6 30,568 1

Finalize GroupAggregate (cost=1,050,986.41..2,491,521.62 rows=9,952,941 width=41) (actual time=6,695.069..6,905.950 rows=30,568 loops=1)

  • Output: g.title, (date_trunc('month'::text, v_1.published)), count(v_1.id)
  • Group Key: (date_trunc('month'::text, v_1.published)), g.title
  • Buffers: shared hit=1800429 read=59972 dirtied=486
  • I/O Timings: read=1077.544
2. 0.000 7,039.206 ↑ 118.4 84,039 1

Gather Merge (cost=1,050,986.41..2,292,462.80 rows=9,952,940 width=41) (actual time=6,695.060..7,039.206 rows=84,039 loops=1)

  • Output: g.title, (date_trunc('month'::text, v_1.published)), (PARTIAL count(v_1.id))
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=8852372 read=368869 dirtied=1279
  • I/O Timings: read=4993.518
3. 435.830 33,645.025 ↑ 148.0 16,808 5

Partial GroupAggregate (cost=1,049,986.35..1,105,971.64 rows=2,488,235 width=41) (actual time=6,580.213..6,729.005 rows=16,808 loops=5)

  • Output: g.title, (date_trunc('month'::text, v_1.published)), PARTIAL count(v_1.id)
  • Group Key: (date_trunc('month'::text, v_1.published)), g.title
  • Buffers: shared hit=8852372 read=368869 dirtied=1279
  • I/O Timings: read=4993.518
  • Worker 0: actual time=6541.362..6682.578 rows=17116 loops=1
  • Buffers: shared hit=1471879 read=115920 dirtied=209
  • I/O Timings: read=1131.244
  • Worker 1: actual time=6509.764..6678.203 rows=16636 loops=1
  • Buffers: shared hit=1909744 read=67638 dirtied=93
  • I/O Timings: read=843.060
  • Worker 2: actual time=6686.121..6850.573 rows=17086 loops=1
  • Buffers: shared hit=1937296 read=69917 dirtied=81
  • I/O Timings: read=861.484
  • Worker 3: actual time=6616.791..6752.847 rows=16395 loops=1
  • Buffers: shared hit=1733024 read=55422 dirtied=410
  • I/O Timings: read=1080.187
4. 3,493.190 33,209.195 ↑ 6.5 380,225 5

Sort (cost=1,049,986.35..1,056,206.94 rows=2,488,235 width=45) (actual time=6,580.198..6,641.839 rows=380,225 loops=5)

  • Output: g.title, (date_trunc('month'::text, v_1.published)), v_1.id
  • Sort Key: (date_trunc('month'::text, v_1.published)), g.title
  • Sort Method: quicksort Memory: 46971kB
  • Worker 0: Sort Method: quicksort Memory: 48211kB
  • Worker 1: Sort Method: quicksort Memory: 46814kB
  • Worker 2: Sort Method: quicksort Memory: 48410kB
  • Worker 3: Sort Method: quicksort Memory: 45256kB
  • Buffers: shared hit=8852372 read=368869 dirtied=1279
  • I/O Timings: read=4993.518
  • Worker 0: actual time=6541.341..6602.749 rows=392314 loops=1
  • Buffers: shared hit=1471879 read=115920 dirtied=209
  • I/O Timings: read=1131.244
  • Worker 1: actual time=6509.742..6575.394 rows=376372 loops=1
  • Buffers: shared hit=1909744 read=67638 dirtied=93
  • I/O Timings: read=843.060
  • Worker 2: actual time=6686.108..6752.767 rows=394463 loops=1
  • Buffers: shared hit=1937296 read=69917 dirtied=81
  • I/O Timings: read=861.484
  • Worker 3: actual time=6616.782..6673.901 rows=359883 loops=1
  • Buffers: shared hit=1733024 read=55422 dirtied=410
  • I/O Timings: read=1080.187
5. 1,086.005 29,716.005 ↑ 6.5 380,225 5

Parallel Hash Join (cost=572,391.29..785,652.55 rows=2,488,235 width=45) (actual time=3,266.791..5,943.201 rows=380,225 loops=5)

  • Output: g.title, date_trunc('month'::text, v_1.published), v_1.id
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=8852313 read=368868 dirtied=1279
  • I/O Timings: read=4993.060
  • Worker 0: actual time=3265.091..5940.471 rows=392314 loops=1
  • Buffers: shared hit=1471865 read=115919 dirtied=209
  • I/O Timings: read=1130.785
  • Worker 1: actual time=3265.076..5943.344 rows=376372 loops=1
  • Buffers: shared hit=1909729 read=67638 dirtied=93
  • I/O Timings: read=843.060
  • Worker 2: actual time=3265.288..5941.404 rows=394463 loops=1
  • Buffers: shared hit=1937281 read=69917 dirtied=81
  • I/O Timings: read=861.484
  • Worker 3: actual time=3264.714..5937.664 rows=359883 loops=1
  • Buffers: shared hit=1733009 read=55422 dirtied=410
  • I/O Timings: read=1080.187
6. 8,187.410 28,470.050 ↑ 6.5 380,225 5

Parallel Hash Join (cost=551,895.48..752,404.00 rows=2,488,235 width=24) (actual time=3,234.662..5,694.010 rows=380,225 loops=5)

  • Output: v_1.published, v_1.id, vg.game_id
  • Hash Cond: (vg.youtube_video_id = v_1.id)
  • Buffers: shared hit=8852267 read=348750 dirtied=1279
  • I/O Timings: read=4914.387
  • Worker 0: actual time=3234.676..5699.351 rows=392314 loops=1
  • Buffers: shared hit=1471854 read=111659 dirtied=209
  • I/O Timings: read=1115.477
  • Worker 1: actual time=3234.674..5694.369 rows=376372 loops=1
  • Buffers: shared hit=1909717 read=63493 dirtied=93
  • I/O Timings: read=827.366
  • Worker 2: actual time=3234.888..5700.949 rows=394463 loops=1
  • Buffers: shared hit=1937270 read=65717 dirtied=81
  • I/O Timings: read=846.109
  • Worker 3: actual time=3234.339..5683.655 rows=359883 loops=1
  • Buffers: shared hit=1732998 read=52272 dirtied=410
  • I/O Timings: read=1065.954
7. 4,194.700 4,194.700 ↑ 1.3 3,447,729 5

Parallel Seq Scan on matching.youtube_video_to_game_2 vg (cost=0.00..172,739.62 rows=4,309,662 width=16) (actual time=0.389..838.940 rows=3,447,729 loops=5)

  • Output: vg.youtube_video_id, vg.game_id, vg.matching_score
  • Buffers: shared hit=6 read=129637
  • I/O Timings: read=2385.557
  • Worker 0: actual time=0.432..830.156 rows=3560529 loops=1
  • Buffers: shared hit=1 read=26804
  • I/O Timings: read=478.241
  • Worker 1: actual time=0.407..842.348 rows=3387174 loops=1
  • Buffers: shared hit=2 read=25459
  • I/O Timings: read=476.347
  • Worker 2: actual time=0.639..830.423 rows=3600173 loops=1
  • Buffers: shared hit=1 read=27082
  • I/O Timings: read=480.213
  • Worker 3: actual time=0.006..845.478 rows=3254345 loops=1
  • Buffers: shared hit=1 read=24463
  • I/O Timings: read=474.094
8. 4,737.900 16,087.940 ↑ 1.3 1,979,340 5

Parallel Hash (cost=520,792.53..520,792.53 rows=2,488,236 width=20) (actual time=3,217.587..3,217.588 rows=1,979,340 loops=5)

  • Output: v_1.published, v_1.id
  • Buckets: 16777216 Batches: 1 Memory Usage: 673440kB
  • Buffers: shared hit=8852217 read=219113 dirtied=1279
  • I/O Timings: read=2528.830
  • Worker 0: actual time=3234.172..3234.172 rows=1892327 loops=1
  • Buffers: shared hit=1471842 read=84855 dirtied=209
  • I/O Timings: read=637.236
  • Worker 1: actual time=3234.166..3234.166 rows=2200735 loops=1
  • Buffers: shared hit=1909704 read=38034 dirtied=93
  • I/O Timings: read=351.019
  • Worker 2: actual time=3234.166..3234.166 rows=2233498 loops=1
  • Buffers: shared hit=1937258 read=38635 dirtied=81
  • I/O Timings: read=365.896
  • Worker 3: actual time=3234.170..3234.171 rows=1751646 loops=1
  • Buffers: shared hit=1732986 read=27809 dirtied=410
  • I/O Timings: read=591.860
9. 983.012 11,350.040 ↑ 1.3 1,979,340 5

Parallel Append (cost=0.12..520,792.53 rows=2,488,236 width=20) (actual time=0.057..2,270.008 rows=1,979,340 loops=5)

  • Buffers: shared hit=8852217 read=219113 dirtied=1279
  • I/O Timings: read=2528.830
  • Worker 0: actual time=0.052..2299.258 rows=1892327 loops=1
  • Buffers: shared hit=1471842 read=84855 dirtied=209
  • I/O Timings: read=637.236
  • Worker 1: actual time=0.085..2195.444 rows=2200735 loops=1
  • Buffers: shared hit=1909704 read=38034 dirtied=93
  • I/O Timings: read=351.019
  • Worker 2: actual time=0.056..2187.789 rows=2233498 loops=1
  • Buffers: shared hit=1937258 read=38635 dirtied=81
  • I/O Timings: read=365.896
  • Worker 3: actual time=0.043..2331.206 rows=1751646 loops=1
  • Buffers: shared hit=1732986 read=27809 dirtied=410
  • I/O Timings: read=591.860
10. 4,501.575 4,501.575 ↑ 1.3 983,076 5

Parallel Index Only Scan using videos_y2019_q2_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q2 v_1 (cost=0.56..168,030.14 rows=1,230,221 width=20) (actual time=0.084..900.315 rows=983,076 loops=5)

  • Output: v_1.published, v_1.id
  • Index Cond: ((v_1.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_1.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 38148
  • Buffers: shared hit=4273362 read=84937 dirtied=181
  • I/O Timings: read=818.284
  • Worker 0: actual time=0.106..181.519 rows=163946 loops=1
  • Buffers: shared hit=145793 read=2810 dirtied=3
  • I/O Timings: read=35.575
  • Worker 1: actual time=0.081..1983.180 rows=2200735 loops=1
  • Buffers: shared hit=1909704 read=38034 dirtied=93
  • I/O Timings: read=351.019
  • Worker 2: actual time=0.053..1976.639 rows=2233498 loops=1
  • Buffers: shared hit=1937258 read=38635 dirtied=81
  • I/O Timings: read=365.896
  • Worker 3: actual time=0.075..181.269 rows=151226 loops=1
  • Buffers: shared hit=133727 read=2607 dirtied=3
  • I/O Timings: read=32.108
11. 1,901.663 1,901.663 ↓ 4.2 1,697,673 1

Parallel Index Only Scan using videos_y2019_q1_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q1 v (cost=0.56..146,239.92 rows=407,164 width=20) (actual time=0.050..1,901.663 rows=1,697,673 loops=1)

  • Output: v.published, v.id
  • Index Cond: ((v.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 8568
  • Buffers: shared hit=1294436 read=82010 dirtied=199
  • I/O Timings: read=593.905
  • Worker 0: actual time=0.050..1901.663 rows=1697673 loops=1
  • Buffers: shared hit=1294436 read=82010 dirtied=199
  • I/O Timings: read=593.905
12. 3,963.780 3,963.780 ↓ 1.3 1,094,549 3

Parallel Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q3 v_2 (cost=0.56..194,080.04 rows=850,851 width=20) (actual time=0.040..1,321.260 rows=1,094,549 loops=3)

  • Output: v_2.published, v_2.id
  • Index Cond: ((v_2.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_2.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 207412
  • Buffers: shared hit=3284418 read=52166 dirtied=899
  • I/O Timings: read=1116.641
  • Worker 0: actual time=0.040..30.829 rows=30708 loops=1
  • Buffers: shared hit=31613 read=35 dirtied=7
  • I/O Timings: read=7.756
  • Worker 3: actual time=0.042..1968.956 rows=1600420 loops=1
  • Buffers: shared hit=1599259 read=25202 dirtied=407
  • I/O Timings: read=559.752
13. 0.010 0.010 ↓ 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_3 (cost=0.12..1.24 rows=1 width=20) (actual time=0.010..0.010 rows=0 loops=1)

  • Output: v_3.published, v_3.id
  • Index Cond: ((v_3.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_3.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=1
14. 19.115 159.950 ↑ 1.6 10,184 5

Parallel Hash (cost=20,287.03..20,287.03 rows=16,703 width=29) (actual time=31.990..31.990 rows=10,184 loops=5)

  • Output: g.title, g.id
  • Buckets: 65536 Batches: 1 Memory Usage: 3840kB
  • Buffers: shared hit=2 read=20118
  • I/O Timings: read=78.672
  • Worker 0: actual time=30.341..30.341 rows=10585 loops=1
  • Buffers: shared read=4260
  • I/O Timings: read=15.308
  • Worker 1: actual time=30.338..30.339 rows=10333 loops=1
  • Buffers: shared hit=1 read=4145
  • I/O Timings: read=15.694
  • Worker 2: actual time=30.331..30.331 rows=10472 loops=1
  • Buffers: shared read=4200
  • I/O Timings: read=15.375
  • Worker 3: actual time=30.301..30.302 rows=7762 loops=1
  • Buffers: shared read=3150
  • I/O Timings: read=14.233
15. 140.835 140.835 ↑ 1.6 10,184 5

Parallel Seq Scan on topic.games g (cost=0.00..20,287.03 rows=16,703 width=29) (actual time=0.013..28.167 rows=10,184 loops=5)

  • Output: g.title, g.id
  • Buffers: shared hit=2 read=20118
  • I/O Timings: read=78.672
  • Worker 0: actual time=0.012..26.543 rows=10585 loops=1
  • Buffers: shared read=4260
  • I/O Timings: read=15.308
  • Worker 1: actual time=0.013..26.934 rows=10333 loops=1
  • Buffers: shared hit=1 read=4145
  • I/O Timings: read=15.694
  • Worker 2: actual time=0.012..26.730 rows=10472 loops=1
  • Buffers: shared read=4200
  • I/O Timings: read=15.375
  • Worker 3: actual time=0.016..26.624 rows=7762 loops=1
  • Buffers: shared read=3150
  • I/O Timings: read=14.233
Planning time : 3.003 ms