explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GTNN

Settings
# exclusive inclusive rows x rows loops node
1. 1.675 890.071 ↑ 1.0 100 1

Limit (cost=201,314.72..201,314.97 rows=100 width=134) (actual time=888.390..890.071 rows=100 loops=1)

2. 12.667 888.396 ↑ 738.2 100 1

Sort (cost=201,314.72..201,499.27 rows=73,817 width=134) (actual time=888.389..888.396 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. 367.959 875.729 ↑ 1.4 52,487 1

HashAggregate (cost=196,278.98..198,493.49 rows=73,817 width=134) (actual time=829.519..875.729 rows=52,487 loops=1)

  • Group Key: v.channel_id, c.name
4. 0.000 507.770 ↓ 14.2 1,047,578 1

Gather (cost=2,161.64..193,141.76 rows=73,817 width=54) (actual time=33.613..507.770 rows=1,047,578 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 601.589 663.245 ↓ 11.4 209,516 5

Nested Loop (cost=1,161.64..184,760.06 rows=18,454 width=54) (actual time=29.914..663.245 rows=209,516 loops=5)

6. 42.894 61.623 ↑ 1.3 17,257 5

Parallel Bitmap Heap Scan on channels c (cost=1,161.08..82,428.87 rows=21,870 width=38) (actual time=29.399..61.623 rows=17,257 loops=5)

  • Recheck Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
  • Heap Blocks: exact=8050
7. 18.729 18.729 ↑ 1.0 86,303 1

Bitmap Index Scan on channels_subscriber_count_idx (cost=0.00..1,139.21 rows=87,478 width=0) (actual time=18.729..18.729 rows=86,303 loops=1)

  • Index Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
8. 0.001 0.033 ↓ 1.1 12 86,286

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

9. 0.015 0.015 ↓ 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.011..0.015 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: 4780
10. 0.016 0.016 ↓ 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.011..0.016 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: 14517
11. 0.001 0.001 ↓ 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 : 3.079 ms