explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dU8H

Settings
# exclusive inclusive rows x rows loops node
1. 73.678 5,883.076 ↑ 1.0 100 1

Limit (cost=793,500.81..793,501.06 rows=100 width=100) (actual time=5,809.395..5,883.076 rows=100 loops=1)

2. 1.311 5,809.398 ↑ 517.0 100 1

Sort (cost=793,500.81..793,630.05 rows=51,699 width=100) (actual time=5,809.393..5,809.398 rows=100 loops=1)

  • Sort Key: (((sum(v.view_count) FILTER (WHERE ((v.published > '2019-08-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-09-01 00:00:00+00'::timestamp with time zone))) - sum(v.view_count) FILTER (WHERE ((v.published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-08-01 00:00:00+00'::timestamp with time zone)))) / NULLIF(sum(v.view_count) FILTER (WHERE ((v.published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-08-01 00:00:00+00'::timestamp with time zone))), '0'::numeric))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 35kB
3. 0.000 5,808.087 ↑ 8.2 6,268 1

Finalize HashAggregate (cost=789,973.94..791,524.91 rows=51,699 width=100) (actual time=5,804.114..5,808.087 rows=6,268 loops=1)

  • Group Key: g.id
4. 118.589 5,866.728 ↑ 11.6 17,889 1

Gather (cost=762,831.96..784,287.05 rows=206,796 width=68) (actual time=5,753.193..5,866.728 rows=17,889 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 46.205 5,748.139 ↑ 14.4 3,578 5

Partial HashAggregate (cost=761,831.96..762,607.45 rows=51,699 width=68) (actual time=5,745.663..5,748.139 rows=3,578 loops=5)

  • Group Key: g.id
6. 40.846 5,701.934 ↑ 6.9 120,641 5

Parallel Hash Join (cost=554,183.50..747,231.92 rows=834,288 width=20) (actual time=3,883.671..5,701.934 rows=120,641 loops=5)

  • Hash Cond: (vg.game_id = g.id)
7. 1,357.805 5,653.731 ↑ 6.9 120,641 5

Parallel Hash Join (cost=552,992.34..743,850.58 rows=834,288 width=20) (actual time=3,876.175..5,653.731 rows=120,641 loops=5)

  • Hash Cond: (vg.youtube_video_id = v.id)
8. 425.271 425.271 ↑ 1.3 3,440,263 5

Parallel Seq Scan on youtube_video_to_game_2 vg (cost=0.00..172,646.29 rows=4,300,329 width=16) (actual time=0.053..425.271 rows=3,440,263 loops=5)

9. 354.617 3,870.655 ↑ 1.3 652,876 5

Parallel Hash (cost=542,563.74..542,563.74 rows=834,288 width=28) (actual time=3,870.655..3,870.655 rows=652,876 loops=5)

  • Buckets: 4194304 Batches: 1 Memory Usage: 237280kB
10. 262.376 3,516.038 ↑ 1.3 652,876 5

Parallel Hash Join (cost=347,681.61..542,563.74 rows=834,288 width=28) (actual time=2,209.216..3,516.038 rows=652,876 loops=5)

  • Hash Cond: (v.channel_id = c.id)
11. 62.655 1,049.780 ↑ 1.3 652,880 5

Parallel Append (cost=0.12..192,692.14 rows=834,326 width=53) (actual time=0.103..1,049.780 rows=652,880 loops=5)

12. 987.115 987.115 ↑ 1.3 652,880 5

Parallel Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v (cost=0.56..188,519.27 rows=834,326 width=53) (actual time=0.095..987.115 rows=652,880 loops=5)

  • Index Cond: ((published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 77792
13. 0.010 0.010 ↓ 0.0 0 3

Parallel Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on videos_default v_1 (cost=0.12..1.24 rows=1 width=60) (actual time=0.010..0.010 rows=0 loops=3)

  • Index Cond: ((published > '2019-07-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
14. 385.188 2,203.882 ↑ 1.2 793,018 5

Parallel Hash (cost=335,292.33..335,292.33 rows=991,133 width=25) (actual time=2,203.882..2,203.882 rows=793,018 loops=5)

  • Buckets: 4194304 Batches: 1 Memory Usage: 281120kB
15. 1,818.694 1,818.694 ↑ 1.2 793,018 5

Parallel Seq Scan on channels c (cost=0.00..335,292.33 rows=991,133 width=25) (actual time=0.008..1,818.694 rows=793,018 loops=5)

16. 2.756 7.357 ↑ 2.1 10,168 5

Parallel Hash (cost=921.90..921.90 rows=21,541 width=4) (actual time=7.357..7.357 rows=10,168 loops=5)

  • Buckets: 65536 Batches: 1 Memory Usage: 2592kB
17. 4.601 4.601 ↑ 2.1 10,168 5

Parallel Index Only Scan using games_pkey on games g (cost=0.29..921.90 rows=21,541 width=4) (actual time=0.123..4.601 rows=10,168 loops=5)

  • Heap Fetches: 159
Planning time : 3.386 ms