explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mp57

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 5,083.590 ↑ 127.1 74,774 1

Finalize GroupAggregate (cost=914,385.01..2,343,393.41 rows=9,503,936 width=65) (actual time=4,806.011..5,083.590 rows=74,774 loops=1)

  • Group Key: g.title, (date_trunc('week'::text, v.published))
2. 0.000 5,155.154 ↑ 52.1 182,289 1

Gather Merge (cost=914,385.01..2,105,795.01 rows=9,503,936 width=65) (actual time=4,805.995..5,155.154 rows=182,289 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
3. 430.085 23,915.730 ↑ 65.2 36,458 5

Partial GroupAggregate (cost=913,384.95..972,784.55 rows=2,375,984 width=65) (actual time=4,642.863..4,783.146 rows=36,458 loops=5)

  • Group Key: g.title, (date_trunc('week'::text, v.published))
4. 3,809.180 23,485.645 ↑ 6.4 368,592 5

Sort (cost=913,384.95..919,324.91 rows=2,375,984 width=41) (actual time=4,642.851..4,697.129 rows=368,592 loops=5)

  • Sort Key: g.title, (date_trunc('week'::text, v.published))
  • Sort Method: quicksort Memory: 41427kB
  • Worker 0: Sort Method: quicksort Memory: 40431kB
  • Worker 1: Sort Method: quicksort Memory: 46182kB
  • Worker 2: Sort Method: quicksort Memory: 44103kB
  • Worker 3: Sort Method: quicksort Memory: 41663kB
5. 948.615 19,676.465 ↑ 6.4 368,592 5

Parallel Hash Join (cost=449,927.61..661,767.13 rows=2,375,984 width=41) (actual time=2,095.640..3,935.293 rows=368,592 loops=5)

  • Hash Cond: (vg.game_id = g.id)
6. 6,995.105 18,687.485 ↑ 6.4 368,592 5

Parallel Hash Join (cost=429,433.85..629,095.94 rows=2,375,984 width=20) (actual time=2,087.417..3,737.497 rows=368,592 loops=5)

  • Hash Cond: (vg.youtube_video_id = v.id)
7. 1,338.810 1,338.810 ↑ 1.2 3,424,274 5

Parallel Seq Scan on youtube_video_to_game_2 vg (cost=0.00..172,320.42 rows=4,280,342 width=16) (actual time=0.006..267.762 rows=3,424,274 loops=5)

8. 3,910.525 10,353.570 ↑ 1.2 1,920,674 5

Parallel Hash (cost=399,734.05..399,734.05 rows=2,375,984 width=28) (actual time=2,070.714..2,070.714 rows=1,920,674 loops=5)

  • Buckets: 16777216 Batches: 1 Memory Usage: 732512kB
9. 760.784 6,443.045 ↑ 1.2 1,920,674 5

Parallel Append (cost=0.55..399,734.05 rows=2,375,984 width=28) (actual time=0.035..1,288.609 rows=1,920,674 loops=5)

10. 951.425 951.425 ↓ 4.3 1,631,685 1

Parallel Index Only Scan using videos_y2019_q1_channel_id_published_id_view_count_comment__idx on videos_y2019_q1 v (cost=0.56..131,597.32 rows=383,588 width=28) (actual time=0.028..951.425 rows=1,631,685 loops=1)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
11. 1,560.175 1,560.175 ↑ 1.2 613,404 5

Parallel Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v_4 (cost=0.56..88,979.31 rows=764,618 width=28) (actual time=0.033..312.035 rows=613,404 loops=5)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
12. 1,254.045 1,254.045 ↓ 1.3 558,503 3

Parallel Index Only Scan using videos_y2019_m04_channel_id_published_id_view_count_comment_idx on videos_y2019_m04 v_2 (cost=0.55..57,344.45 rows=420,740 width=28) (actual time=0.026..418.015 rows=558,503 loops=3)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
13. 1,076.728 1,076.728 ↓ 2.0 819,063 2

Parallel Index Only Scan using videos_y2019_m03_channel_id_published_id_view_count_comment_idx on videos_y2019_m03 v_1 (cost=0.55..55,422.24 rows=405,785 width=28) (actual time=0.027..538.364 rows=819,063 loops=2)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
14. 839.877 839.877 ↓ 4.0 1,591,029 1

Parallel Index Only Scan using videos_y2019_m05_channel_id_published_id_view_count_comment_idx on videos_y2019_m05 v_3 (cost=0.55..54,509.58 rows=401,253 width=28) (actual time=0.019..839.877 rows=1,591,029 loops=1)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
15. 0.011 0.011 ↓ 0.0 0 1

Parallel Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on videos_default v_5 (cost=0.12..1.24 rows=1 width=28) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
16. 14.295 40.365 ↑ 1.6 10,131 5

Parallel Hash (cost=20,286.12..20,286.12 rows=16,612 width=29) (actual time=8.072..8.073 rows=10,131 loops=5)

  • Buckets: 65536 Batches: 1 Memory Usage: 3808kB
17. 26.070 26.070 ↑ 1.6 10,131 5

Parallel Seq Scan on games g (cost=0.00..20,286.12 rows=16,612 width=29) (actual time=0.005..5.214 rows=10,131 loops=5)

Planning time : 2.432 ms