explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kMec

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 1,667.667 ↑ 1.0 100 1

Limit (cost=742,670.27..742,670.52 rows=100 width=128) (actual time=1,667.652..1,667.667 rows=100 loops=1)

2.          

CTE previous_period

3. 25.679 905.225 ↓ 1.3 46,153 1

Finalize GroupAggregate (cost=182,686.11..187,783.57 rows=35,373 width=70) (actual time=799.726..905.225 rows=46,153 loops=1)

  • Group Key: c.id
4. 0.000 879.546 ↓ 1.3 46,153 1

Gather Merge (cost=182,686.11..187,076.11 rows=35,372 width=70) (actual time=799.711..879.546 rows=46,153 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 91.085 3,984.450 ↓ 1.0 9,231 5

Partial GroupAggregate (cost=181,686.05..181,862.91 rows=8,843 width=70) (actual time=769.840..796.890 rows=9,231 loops=5)

  • Group Key: c.id
6. 1,143.070 3,893.365 ↓ 11.6 102,396 5

Sort (cost=181,686.05..181,708.16 rows=8,843 width=46) (actual time=769.827..778.673 rows=102,396 loops=5)

  • Sort Key: c.id
  • Sort Method: quicksort Memory: 13275kB
  • Worker 0: Sort Method: quicksort Memory: 12951kB
  • Worker 1: Sort Method: quicksort Memory: 14480kB
  • Worker 2: Sort Method: quicksort Memory: 12714kB
  • Worker 3: Sort Method: quicksort Memory: 14065kB
7. 105.040 2,750.295 ↓ 11.6 102,396 5

Nested Loop (cost=1,161.64..181,106.38 rows=8,843 width=46) (actual time=29.584..550.059 rows=102,396 loops=5)

8. 297.108 315.560 ↑ 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.187..63.112 rows=17,257 loops=5)

  • Recheck Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
  • Heap Blocks: exact=14876
9. 18.452 18.452 ↑ 1.0 86,582 1

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

  • Index Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
10. 86.285 2,329.695 ↑ 1.2 6 86,285

Append (cost=0.56..4.44 rows=7 width=33) (actual time=0.017..0.027 rows=6 loops=86,285)

11. 1,294.275 1,294.275 ↓ 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..1.69 rows=5 width=33) (actual time=0.011..0.015 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-07-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 5742
12. 862.850 862.850 ↓ 0.0 0 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..1.58 rows=1 width=33) (actual time=0.010..0.010 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-07-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 1
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=40) (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-07-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
14.          

CTE current_period

15. 141.531 474.690 ↓ 1.2 45,680 1

HashAggregate (cost=151,296.79..151,777.34 rows=38,444 width=70) (actual time=457.086..474.690 rows=45,680 loops=1)

  • Group Key: c_1.id
16. 0.000 333.159 ↓ 13.9 535,586 1

Gather (cost=2,161.63..151,104.57 rows=38,444 width=46) (actual time=35.950..333.159 rows=535,586 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
17. 318.282 378.992 ↓ 11.1 107,117 5

Nested Loop (cost=1,161.63..146,260.17 rows=9,611 width=46) (actual time=30.500..378.992 rows=107,117 loops=5)

18. 41.600 60.693 ↑ 1.3 17,257 5

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

  • Recheck Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
  • Heap Blocks: exact=10216
19. 19.093 19.093 ↑ 1.0 86,582 1

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

  • Index Cond: ((subscriber_count > 10000) AND (subscriber_count < 100000))
20. 0.000 0.017 ↑ 1.0 6 86,285

Append (cost=0.56..2.86 rows=6 width=33) (actual time=0.012..0.017 rows=6 loops=86,285)

21. 0.016 0.016 ↓ 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_3 (cost=0.56..1.69 rows=5 width=33) (actual time=0.011..0.016 rows=6 loops=86,285)

  • Index Cond: ((channel_id = c_1.id) AND (published >= '2019-07-01 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 19293
22. 0.001 0.001 ↓ 0.0 0 86,285

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

  • Index Cond: ((channel_id = c_1.id) AND (published >= '2019-07-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
23. 9.844 1,667.657 ↑ 67,994.0 100 1

Sort (cost=403,109.36..420,107.86 rows=6,799,398 width=128) (actual time=1,667.651..1,667.657 rows=100 loops=1)

  • Sort Key: (((current_period.view_count - previous_period.view_count) / previous_period.view_count)) DESC
  • Sort Method: top-N heapsort Memory: 39kB
24. 57.861 1,657.813 ↑ 172.8 39,346 1

Merge Join (cost=7,076.44..143,241.26 rows=6,799,398 width=128) (actual time=1,590.424..1,657.813 rows=39,346 loops=1)

  • Merge Cond: (previous_period.id = current_period.id)
25. 22.902 944.398 ↓ 1.3 46,153 1

Sort (cost=3,379.95..3,468.39 rows=35,373 width=64) (actual time=940.923..944.398 rows=46,153 loops=1)

  • Sort Key: previous_period.id
  • Sort Method: quicksort Memory: 5142kB
26. 921.496 921.496 ↓ 1.3 46,153 1

CTE Scan on previous_period (cost=0.00..707.46 rows=35,373 width=64) (actual time=799.730..921.496 rows=46,153 loops=1)

27. 163.974 655.554 ↓ 1.2 45,680 1

Sort (cost=3,696.48..3,792.59 rows=38,444 width=96) (actual time=649.487..655.554 rows=45,680 loops=1)

  • Sort Key: current_period.id
  • Sort Method: quicksort Memory: 5847kB
28. 491.580 491.580 ↓ 1.2 45,680 1

CTE Scan on current_period (cost=0.00..768.88 rows=38,444 width=96) (actual time=457.091..491.580 rows=45,680 loops=1)

Planning time : 5.094 ms