explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VPOp

Settings
# exclusive inclusive rows x rows loops node
1. 1.738 82,841.502 ↑ 1.0 100 1

Limit (cost=440,917.99..440,918.24 rows=100 width=134) (actual time=82,839.759..82,841.502 rows=100 loops=1)

2. 17.936 82,839.764 ↑ 719.9 100 1

Sort (cost=440,917.99..441,097.96 rows=71,990 width=134) (actual time=82,839.758..82,839.764 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. 84.755 82,821.828 ↑ 1.4 52,484 1

Finalize GroupAggregate (cost=424,597.25..438,166.58 rows=71,990 width=134) (actual time=82,577.633..82,821.828 rows=52,484 loops=1)

  • Group Key: v.channel_id, c.name
4. 0.000 82,737.073 ↑ 1.4 52,484 1

Gather Merge (cost=424,597.25..433,847.12 rows=71,992 width=102) (actual time=82,577.602..82,737.073 rows=52,484 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 305.755 412,772.740 ↑ 1.7 10,497 5

Partial GroupAggregate (cost=423,597.20..424,272.12 rows=17,998 width=102) (actual time=82,473.794..82,554.548 rows=10,497 loops=5)

  • Group Key: v.channel_id, c.name
6. 2,528.105 412,466.985 ↓ 11.6 209,523 5

Sort (cost=423,597.20..423,642.19 rows=17,998 width=54) (actual time=82,473.768..82,493.397 rows=209,523 loops=5)

  • Sort Key: v.channel_id, c.name
  • Sort Method: quicksort Memory: 34371kB
  • Worker 0: Sort Method: quicksort Memory: 34232kB
  • Worker 1: Sort Method: quicksort Memory: 34594kB
  • Worker 2: Sort Method: quicksort Memory: 34405kB
  • Worker 3: Sort Method: quicksort Memory: 34304kB
7. 214.230 409,938.880 ↓ 11.6 209,523 5

Nested Loop (cost=104,983.99..422,325.14 rows=17,998 width=54) (actual time=15,478.662..81,987.776 rows=209,523 loops=5)

8. 62,410.201 77,872.540 ↑ 1.2 17,257 5

Parallel Bitmap Heap Scan on channels c (cost=104,983.43..184,837.71 rows=21,428 width=38) (actual time=15,477.178..15,574.508 rows=17,257 loops=5)

  • Recheck Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
  • Heap Blocks: exact=17139
9. 15,462.339 15,462.339 ↓ 1.2 99,951 1

Bitmap Index Scan on channels_mat_country_subscriber_count_idx (cost=0.00..104,962.01 rows=85,714 width=0) (actual time=15,462.339..15,462.339 rows=99,951 loops=1)

  • Index Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
10. 345.140 331,852.110 ↓ 1.1 12 86,285

Append (cost=0.56..10.97 rows=11 width=41) (actual time=0.370..3.846 rows=12 loops=86,285)

11. 151,689.030 151,689.030 ↓ 1.2 6 86,285

Index Only Scan using videos_y2019_q2_channel_id_published_id_view_count_comment__idx on videos_y2019_q2 v (cost=0.56..4.35 rows=5 width=41) (actual time=0.309..1.758 rows=6 loops=86,285)

  • 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: 274960
12. 179,731.655 179,731.655 ↓ 1.2 6 86,285

Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v_1 (cost=0.56..5.42 rows=5 width=41) (actual time=0.253..2.083 rows=6 loops=86,285)

  • 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: 381743
13. 86.285 86.285 ↓ 0.0 0 86,285

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,285)

  • 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 : 2.820 ms