explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o4EW

Settings
# exclusive inclusive rows x rows loops node
1. 1.603 23,621.508 ↑ 1.0 100 1

Limit (cost=303,830.01..303,830.26 rows=100 width=134) (actual time=23,619.901..23,621.508 rows=100 loops=1)

2. 14.578 23,619.905 ↑ 723.1 100 1

Sort (cost=303,830.01..304,010.78 rows=72,310 width=134) (actual time=23,619.899..23,619.905 rows=100 loops=1)

  • Sort Key: (((sum(CASE WHEN ((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)) THEN v.view_count ELSE '0'::bigint END) - sum(CASE WHEN ((v.published >= '2019-06-01 00:00:00+00'::timestamp with time zone) AND (v.published <= '2019-07-01 00:00:00+00'::timestamp with time zone)) THEN v.view_count ELSE '0'::bigint END)) / NULLIF(sum(CASE WHEN ((v.published >= '2019-06-01 00:00:00+00'::timestamp with time zone) AND (v.published <= '2019-07-01 00:00:00+00'::timestamp with time zone)) THEN v.view_count ELSE '0'::bigint END), '0'::numeric))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 47kB
3. 68.513 23,605.327 ↑ 1.4 52,487 1

Finalize GroupAggregate (cost=287,436.73..301,066.37 rows=72,310 width=134) (actual time=23,403.496..23,605.327 rows=52,487 loops=1)

  • Group Key: v.channel_id, c.name
4. 0.000 23,536.814 ↑ 1.4 52,487 1

Gather Merge (cost=287,436.73..296,727.71 rows=72,312 width=102) (actual time=23,403.475..23,536.814 rows=52,487 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 252.290 116,913.265 ↑ 1.7 10,497 5

Partial GroupAggregate (cost=286,436.67..287,114.60 rows=18,078 width=102) (actual time=23,314.597..23,382.653 rows=10,497 loops=5)

  • Group Key: v.channel_id, c.name
6. 2,076.290 116,660.975 ↓ 11.6 209,515 5

Sort (cost=286,436.67..286,481.87 rows=18,078 width=54) (actual time=23,314.574..23,332.195 rows=209,515 loops=5)

  • Sort Key: v.channel_id, c.name
  • Sort Method: quicksort Memory: 35121kB
  • Worker 0: Sort Method: quicksort Memory: 32889kB
  • Worker 1: Sort Method: quicksort Memory: 35772kB
  • Worker 2: Sort Method: quicksort Memory: 34302kB
  • Worker 3: Sort Method: quicksort Memory: 33810kB
7. 118.244 114,584.685 ↓ 11.6 209,515 5

Nested Loop (cost=105,037.89..285,158.38 rows=18,078 width=54) (actual time=15,712.956..22,916.937 rows=209,515 loops=5)

8. 64,596.765 80,297.185 ↑ 1.2 17,257 5

Parallel Bitmap Heap Scan on channels c (cost=105,037.33..184,891.61 rows=21,428 width=38) (actual time=15,711.091..16,059.437 rows=17,257 loops=5)

  • Recheck Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
  • Heap Blocks: exact=18148
9. 15,700.420 15,700.420 ↓ 1.2 101,750 1

Bitmap Index Scan on channels_mat_country_subscriber_count_idx (cost=0.00..105,015.90 rows=85,714 width=0) (actual time=15,700.420..15,700.420 rows=101,750 loops=1)

  • Index Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
10. 172.572 34,169.256 ↓ 1.1 12 86,286

Append (cost=0.56..4.57 rows=11 width=41) (actual time=0.316..0.396 rows=12 loops=86,286)

11. 31,580.676 31,580.676 ↓ 1.2 6 86,286

Index Only Scan using videos_y2019_q2_channel_id_published_id_view_count_comment__idx on videos_y2019_q2 v (cost=0.56..1.69 rows=5 width=41) (actual time=0.307..0.366 rows=6 loops=86,286)

  • Index Cond: ((channel_id = c.id) AND (published >= '2019-06-01 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 5138
12. 2,329.722 2,329.722 ↓ 1.2 6 86,286

Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v_1 (cost=0.56..1.69 rows=5 width=41) (actual time=0.019..0.027 rows=6 loops=86,286)

  • Index Cond: ((channel_id = c.id) AND (published >= '2019-06-01 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 13301
13. 86.286 86.286 ↓ 0.0 0 86,286

Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on videos_default v_2 (cost=0.12..1.14 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=86,286)

  • Index Cond: ((channel_id = c.id) AND (published >= '2019-06-01 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
Planning time : 6.382 ms