explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DOF5

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 3,546.797 ↑ 1.0 100 1

Limit (cost=8,736,204.77..8,736,205.02 rows=100 width=128) (actual time=3,546.782..3,546.797 rows=100 loops=1)

2.          

CTE previous_period

3. 436.544 1,151.116 ↑ 1.5 117,064 1

HashAggregate (cost=398,029.27..400,157.71 rows=170,275 width=70) (actual time=1,101.836..1,151.116 rows=117,064 loops=1)

  • Group Key: c.id
4. 0.000 714.572 ↓ 9.1 1,542,727 1

Gather (cost=251,763.43..397,177.89 rows=170,275 width=46) (actual time=408.242..714.572 rows=1,542,727 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 116.971 856.574 ↓ 7.2 308,545 5

Parallel Hash Join (cost=250,763.43..379,150.39 rows=42,569 width=46) (actual time=404.035..856.574 rows=308,545 loops=5)

  • Hash Cond: (v.channel_id = c.id)
6. 28.533 336.620 ↑ 1.3 333,195 5

Parallel Append (cost=0.12..127,226.48 rows=442,139 width=33) (actual time=0.041..336.620 rows=333,195 loops=5)

7. 308.082 308.082 ↑ 1.3 333,195 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..125,014.54 rows=442,139 width=33) (actual time=0.038..308.082 rows=333,195 loops=5)

  • Index Cond: ((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: 80573
8. 0.005 0.005 ↓ 0.0 0 2

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=40) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: ((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
9. 30.603 402.983 ↑ 1.3 75,528 5

Parallel Hash (cost=249,570.14..249,570.14 rows=95,453 width=38) (actual time=402.983..402.983 rows=75,528 loops=5)

  • Buckets: 524288 Batches: 1 Memory Usage: 31584kB
10. 278.748 372.380 ↑ 1.3 75,528 5

Parallel Bitmap Heap Scan on channels c (cost=5,254.89..249,570.14 rows=95,453 width=38) (actual time=165.732..372.380 rows=75,528 loops=5)

  • Recheck Cond: ((subscriber_count >= 1000) AND (subscriber_count <= 100000000))
  • Heap Blocks: exact=47372
11. 93.632 93.632 ↓ 1.2 449,404 1

Bitmap Index Scan on channels_subscriber_count_idx (cost=0.00..5,159.44 rows=381,811 width=0) (actual time=93.632..93.632 rows=449,404 loops=1)

  • Index Cond: ((subscriber_count >= 1000) AND (subscriber_count <= 100000000))
12.          

CTE current_period

13. 439.422 1,145.372 ↑ 1.4 108,130 1

HashAggregate (cost=393,452.37..395,363.95 rows=152,926 width=70) (actual time=1,102.382..1,145.372 rows=108,130 loops=1)

  • Group Key: c_1.id
14. 0.000 705.950 ↓ 9.8 1,493,235 1

Gather (cost=251,763.43..392,687.74 rows=152,926 width=46) (actual time=419.661..705.950 rows=1,493,235 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
15. 109.411 834.149 ↓ 7.8 298,647 5

Parallel Hash Join (cost=250,763.43..376,395.14 rows=38,232 width=46) (actual time=415.468..834.149 rows=298,647 loops=5)

  • Hash Cond: (v_2.channel_id = c_1.id)
16. 27.984 310.227 ↑ 1.2 319,299 5

Parallel Append (cost=0.12..124,589.48 rows=397,092 width=33) (actual time=0.043..310.227 rows=319,299 loops=5)

17. 282.238 282.238 ↑ 1.2 319,299 5

Parallel Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v_2 (cost=0.56..122,602.78 rows=397,092 width=33) (actual time=0.041..282.238 rows=319,299 loops=5)

  • Index Cond: ((published > '2019-08-01 00:00:00+00'::timestamp with time zone) AND (published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 83923
18. 0.005 0.005 ↓ 0.0 0 1

Parallel Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on videos_default v_3 (cost=0.12..1.24 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((published > '2019-08-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
19. 30.834 414.511 ↑ 1.3 75,528 5

Parallel Hash (cost=249,570.14..249,570.14 rows=95,453 width=38) (actual time=414.511..414.511 rows=75,528 loops=5)

  • Buckets: 524288 Batches: 1 Memory Usage: 31584kB
20. 295.390 383.677 ↑ 1.3 75,528 5

Parallel Bitmap Heap Scan on channels c_1 (cost=5,254.89..249,570.14 rows=95,453 width=38) (actual time=164.573..383.677 rows=75,528 loops=5)

  • Recheck Cond: ((subscriber_count >= 1000) AND (subscriber_count <= 100000000))
  • Heap Blocks: exact=47550
21. 88.287 88.287 ↓ 1.2 449,402 1

Bitmap Index Scan on channels_subscriber_count_idx (cost=0.00..5,159.44 rows=381,811 width=0) (actual time=88.287..88.287 rows=449,402 loops=1)

  • Index Cond: ((subscriber_count >= 1000) AND (subscriber_count <= 100000000))
22. 23.051 3,546.786 ↑ 1,301,973.7 100 1

Sort (cost=7,940,683.12..8,266,176.55 rows=130,197,373 width=128) (actual time=3,546.781..3,546.786 rows=100 loops=1)

  • Sort Key: (((current_period.view_count - previous_period.view_count) / NULLIF(previous_period.view_count, '0'::numeric))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 39kB
23. 152.994 3,523.735 ↑ 1,435.6 90,690 1

Merge Join (cost=34,427.62..2,964,633.14 rows=130,197,373 width=128) (actual time=3,333.131..3,523.735 rows=90,690 loops=1)

  • Merge Cond: (current_period.id = previous_period.id)
24. 466.298 1,649.010 ↑ 1.4 108,130 1

Sort (cost=16,227.34..16,609.66 rows=152,926 width=96) (actual time=1,630.845..1,649.010 rows=108,130 loops=1)

  • Sort Key: current_period.id
  • Sort Method: quicksort Memory: 13201kB
25. 1,182.712 1,182.712 ↑ 1.4 108,130 1

CTE Scan on current_period (cost=0.00..3,058.52 rows=152,926 width=96) (actual time=1,102.385..1,182.712 rows=108,130 loops=1)

26. 527.123 1,721.731 ↑ 1.5 117,064 1

Sort (cost=18,200.28..18,625.96 rows=170,275 width=64) (actual time=1,702.269..1,721.731 rows=117,064 loops=1)

  • Sort Key: previous_period.id
  • Sort Method: quicksort Memory: 12218kB
27. 1,194.608 1,194.608 ↑ 1.5 117,064 1

CTE Scan on previous_period (cost=0.00..3,405.50 rows=170,275 width=64) (actual time=1,101.841..1,194.608 rows=117,064 loops=1)

Planning time : 5.144 ms