explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BPGo

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

Finalize GroupAggregate (cost=914,385.01..2,343,393.41 rows=9,503,936 width=65) (actual time=6,187.374..6,480.168 rows=74,774 loops=1)

  • Group Key: g.title, (date_trunc('week'::text, v.published))
2. 0.000 6,540.861 ↑ 52.1 182,309 1

Gather Merge (cost=914,385.01..2,105,795.01 rows=9,503,936 width=65) (actual time=6,187.361..6,540.861 rows=182,309 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
3. 432.635 31,095.705 ↑ 65.2 36,462 5

Partial GroupAggregate (cost=913,384.95..972,784.55 rows=2,375,984 width=65) (actual time=6,078.374..6,219.141 rows=36,462 loops=5)

  • Group Key: g.title, (date_trunc('week'::text, v.published))
4. 3,924.045 30,663.070 ↑ 6.4 368,592 5

Sort (cost=913,384.95..919,324.91 rows=2,375,984 width=41) (actual time=6,078.362..6,132.614 rows=368,592 loops=5)

  • Sort Key: g.title, (date_trunc('week'::text, v.published))
  • Sort Method: quicksort Memory: 39636kB
  • Worker 0: Sort Method: quicksort Memory: 42635kB
  • Worker 1: Sort Method: quicksort Memory: 42779kB
  • Worker 2: Sort Method: quicksort Memory: 46809kB
  • Worker 3: Sort Method: quicksort Memory: 41947kB
5. 942.590 26,739.025 ↑ 6.4 368,592 5

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

  • Hash Cond: (vg.game_id = g.id)
6. 6,984.705 24,583.835 ↑ 6.4 368,592 5

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

  • Hash Cond: (vg.youtube_video_id = v.id)
7. 1,313.085 1,313.085 ↑ 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.008..262.617 rows=3,424,274 loops=5)

8. 4,168.655 16,286.045 ↑ 1.2 1,920,674 5

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

  • Buckets: 16777216 Batches: 1 Memory Usage: 732544kB
9. 788.833 12,117.390 ↑ 1.2 1,920,674 5

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

10. 1,989.106 1,989.106 ↓ 2.1 815,842 2

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.618..994.553 rows=815,842 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
11. 2,315.818 2,315.818 ↓ 2.0 1,533,510 2

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.626..1,157.909 rows=1,533,510 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
12. 1,258.277 1,258.277 ↓ 4.0 1,675,510 1

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.695..1,258.277 rows=1,675,510 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
13. 4,733.710 4,733.710 ↑ 1.2 327,625 5

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.610..946.742 rows=327,625 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
14. 1,031.638 1,031.638 ↓ 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.062..1,031.638 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.008 0.008 ↓ 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.008..0.008 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. 18.855 1,212.600 ↑ 1.6 10,131 5

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3840kB
17. 1,193.745 1,193.745 ↑ 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.207..238.749 rows=10,131 loops=5)

Planning time : 2.435 ms