explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kRHp

Settings
# exclusive inclusive rows x rows loops node
1. 5.259 35,277.962 ↓ 5,174.0 5,174 1

Sort (cost=204,670.27..204,670.27 rows=1 width=108) (actual time=35,276.395..35,277.962 rows=5,174 loops=1)

  • Sort Key: x.agg_score_percentile DESC
  • Sort Method: quicksort Memory: 920kB
2.          

CTE base_streams

3. 27.504 17,298.357 ↓ 26.8 5,174 1

Subquery Scan on base_streams_x (cost=188,120.91..189,473.31 rows=193 width=40) (actual time=17,146.614..17,298.357 rows=5,174 loops=1)

  • Filter: (base_streams_x.rankk = 1)
  • Rows Removed by Filter: 106,571
4. 93.652 17,270.853 ↓ 2.9 111,745 1

WindowAgg (cost=188,120.91..188,990.31 rows=38,640 width=40) (actual time=17,146.607..17,270.853 rows=111,745 loops=1)

5. 121.739 17,177.201 ↓ 2.9 111,745 1

Sort (cost=188,120.91..188,217.51 rows=38,640 width=32) (actual time=17,146.597..17,177.201 rows=111,745 loops=1)

  • Sort Key: base_streams_cte.nielsen_song_group_id, base_streams_cte.year, base_streams_cte.week
  • Sort Method: quicksort Memory: 11,803kB
6. 16,442.314 17,055.462 ↓ 2.9 111,745 1

Bitmap Heap Scan on nielsen_song_group_metrics base_streams_cte (cost=9,128.45..185,176.96 rows=38,640 width=32) (actual time=621.902..17,055.462 rows=111,745 loops=1)

  • Recheck Cond: ((this_period >= 150,000) AND (this_period <= 300,000))
  • Filter: ((year > 2017) AND (metric_type = 4) AND ((year < 2020) OR ((year = 2,020) AND (week <= 29))))
  • Rows Removed by Filter: 238,456
  • Heap Blocks: exact=38,047
7. 613.148 613.148 ↓ 1.0 350,201 1

Bitmap Index Scan on index_nielsen_song_group_metrics_on_this_period_gist (cost=0.00..9,118.79 rows=339,037 width=0) (actual time=613.148..613.148 rows=350,201 loops=1)

  • Index Cond: ((this_period >= 150,000) AND (this_period <= 300,000))
8.          

CTE base_data

9. 372.061 32,151.575 ↓ 12,522.0 400,703 1

WindowAgg (cost=15,192.95..15,194.31 rows=32 width=60) (actual time=31,307.373..32,151.575 rows=400,703 loops=1)

10. 346.204 31,779.514 ↓ 12,522.0 400,703 1

WindowAgg (cost=15,192.95..15,193.67 rows=32 width=52) (actual time=31,307.357..31,779.514 rows=400,703 loops=1)

11. 506.765 31,433.310 ↓ 12,522.0 400,703 1

Sort (cost=15,192.95..15,193.03 rows=32 width=44) (actual time=31,307.349..31,433.310 rows=400,703 loops=1)

  • Sort Key: nielsen_song_group_metrics.nielsen_song_group_id, nielsen_song_group_metrics.year, nielsen_song_group_metrics.week
  • Sort Method: external merge Disk: 21,144kB
12. 1,218.408 30,926.545 ↓ 12,522.0 400,703 1

Nested Loop (cost=6.71..15,192.15 rows=32 width=44) (actual time=17,308.994..30,926.545 rows=400,703 loops=1)

  • Join Filter: ((base_streams.metric_type = nielsen_song_group_metrics.metric_type) AND ((nielsen_song_groups.is_comparable AND (nielsen_song_group_metrics.metric_type = 4) AND (nielsen_song_group_metrics.year > base_streams.year)) OR ((nielsen_song_group_metrics.year = base_streams.year) AND (nielsen_song_group_metrics.week > base_streams.week))))
  • Rows Removed by Join Filter: 3,029,130
13. 18.488 17,337.103 ↓ 26.8 5,174 1

Hash Join (cost=6.27..731.43 rows=193 width=33) (actual time=17,306.854..17,337.103 rows=5,174 loops=1)

  • Hash Cond: (nielsen_song_groups.id = base_streams.nielsen_song_group_id)
14. 14.619 14.619 ↑ 1.3 18,930 1

Seq Scan on nielsen_song_groups (cost=0.00..633.98 rows=23,798 width=9) (actual time=2.840..14.619 rows=18,930 loops=1)

15. 2.082 17,303.996 ↓ 26.8 5,174 1

Hash (cost=3.86..3.86 rows=193 width=24) (actual time=17,303.996..17,303.996 rows=5,174 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 368kB
16. 17,301.914 17,301.914 ↓ 26.8 5,174 1

CTE Scan on base_streams (cost=0.00..3.86 rows=193 width=24) (actual time=17,146.617..17,301.914 rows=5,174 loops=1)

17. 12,371.034 12,371.034 ↑ 1.5 663 5,174

Index Scan using index_nielsen_song_group_metrics_on_nielsen_song_group_id on nielsen_song_group_metrics (cost=0.43..53.08 rows=971 width=24) (actual time=0.159..2.391 rows=663 loops=5,174)

  • Index Cond: (nielsen_song_group_id = nielsen_song_groups.id)
18.          

CTE agg_score_data

19. 392.944 33,183.979 ↓ 21,425.6 235,682 1

WindowAgg (cost=0.91..1.24 rows=11 width=92) (actual time=32,721.564..33,183.979 rows=235,682 loops=1)

20. 292.647 32,791.035 ↓ 21,425.6 235,682 1

Sort (cost=0.91..0.94 rows=11 width=52) (actual time=32,721.497..32,791.035 rows=235,682 loops=1)

  • Sort Key: base_data.id
  • Sort Method: external sort Disk: 15,208kB
21. 32,498.388 32,498.388 ↓ 21,425.6 235,682 1

CTE Scan on base_data (cost=0.00..0.72 rows=11 width=52) (actual time=31,307.382..32,498.388 rows=235,682 loops=1)

  • Filter: (week_number <= 52)
  • Rows Removed by Filter: 165,021
22.          

CTE ranked_agg_score_data

23. 329.826 34,421.311 ↓ 21,425.6 235,682 1

WindowAgg (cost=0.41..0.63 rows=11 width=100) (actual time=33,993.069..34,421.311 rows=235,682 loops=1)

24. 666.922 34,091.485 ↓ 21,425.6 235,682 1

Sort (cost=0.41..0.44 rows=11 width=92) (actual time=33,993.021..34,091.485 rows=235,682 loops=1)

  • Sort Key: agg_score_data.max_num_weeks, agg_score_data.agg_score
  • Sort Method: external merge Disk: 20,728kB
25. 33,424.563 33,424.563 ↓ 21,425.6 235,682 1

CTE Scan on agg_score_data (cost=0.00..0.22 rows=11 width=92) (actual time=32,721.567..33,424.563 rows=235,682 loops=1)

26. 57.246 35,272.703 ↓ 5,174.0 5,174 1

Subquery Scan on x (cost=0.41..0.77 rows=1 width=108) (actual time=34,931.603..35,272.703 rows=5,174 loops=1)

  • Filter: (x.rankk = 1)
  • Rows Removed by Filter: 230,508
27. 208.406 35,215.457 ↓ 21,425.6 235,682 1

WindowAgg (cost=0.41..0.63 rows=11 width=108) (actual time=34,931.594..35,215.457 rows=235,682 loops=1)

28. 341.167 35,007.051 ↓ 21,425.6 235,682 1

Sort (cost=0.41..0.44 rows=11 width=100) (actual time=34,931.583..35,007.051 rows=235,682 loops=1)

  • Sort Key: ranked_agg_score_data.id, ranked_agg_score_data.week_number DESC
  • Sort Method: external merge Disk: 22,576kB
29. 34,665.884 34,665.884 ↓ 21,425.6 235,682 1

CTE Scan on ranked_agg_score_data (cost=0.00..0.22 rows=11 width=100) (actual time=33,993.073..34,665.884 rows=235,682 loops=1)

Planning time : 11.017 ms
Execution time : 35,324.263 ms