explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k9OT

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 209.085 ↓ 2.2 28 1

Limit (cost=272,029.67..272,243.29 rows=13 width=268) (actual time=209.021..209.085 rows=28 loops=1)

2. 0.025 209.081 ↓ 2.2 28 1

Result (cost=272,029.67..272,243.29 rows=13 width=268) (actual time=209.019..209.081 rows=28 loops=1)

3. 0.048 209.000 ↓ 2.2 28 1

Sort (cost=272,029.67..272,029.70 rows=13 width=260) (actual time=208.998..209.000 rows=28 loops=1)

  • Sort Key: window_results.release_date DESC
  • Sort Method: quicksort Memory: 34kB
4. 0.015 208.952 ↓ 2.2 28 1

Subquery Scan on window_results (cost=271,863.24..272,029.43 rows=13 width=260) (actual time=208.679..208.952 rows=28 loops=1)

  • Filter: (window_results.row_number = 1)
  • Rows Removed by Filter: 30
5. 0.284 208.937 ↑ 45.8 58 1

WindowAgg (cost=271,863.24..271,996.19 rows=2,659 width=304) (actual time=208.677..208.937 rows=58 loops=1)

6. 1.347 208.653 ↑ 45.8 58 1

Sort (cost=271,863.24..271,869.89 rows=2,659 width=326) (actual time=208.652..208.653 rows=58 loops=1)

  • Sort Key: (COALESCE(sp_tracks.isrc, (sp_tracks.id)::character varying(255))), sp_tracks.popularity DESC, sp_tracks.updated_at DESC
  • Sort Method: quicksort Memory: 44kB
7. 20.739 207.306 ↑ 45.8 58 1

Hash Right Join (cost=39,185.06..271,711.99 rows=2,659 width=326) (actual time=93.495..207.306 rows=58 loops=1)

  • Hash Cond: ((COALESCE(occ_processed_chart_listings.expected_isrc, occ_processed_chart_listings.primary_isrc))::text = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::text)
8. 119.418 182.614 ↑ 1.5 70,982 1

Bitmap Heap Scan on occ_processed_chart_listings (cost=39,116.79..270,932.37 rows=106,370 width=61) (actual time=69.673..182.614 rows=70,982 loops=1)

  • Recheck Cond: (occ_chart_source_id = 1)
  • Heap Blocks: exact=24,179
9. 63.196 63.196 ↑ 1.5 70,982 1

Bitmap Index Scan on index_occ_processed_chart_listings_on_pri_isrc_and_chart_source (cost=0.00..39,090.19 rows=106,370 width=0) (actual time=63.196..63.196 rows=70,982 loops=1)

  • Index Cond: (occ_chart_source_id = 1)
10. 0.132 3.953 ↓ 11.6 58 1

Hash (cost=68.21..68.21 rows=5 width=278) (actual time=3.953..3.953 rows=58 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
11. 0.136 3.821 ↓ 11.6 58 1

Nested Loop Left Join (cost=2.39..68.21 rows=5 width=278) (actual time=0.201..3.821 rows=58 loops=1)

12. 0.063 2.641 ↓ 11.6 58 1

Nested Loop Left Join (cost=2.11..66.71 rows=5 width=270) (actual time=0.168..2.641 rows=58 loops=1)

13. 0.041 1.570 ↓ 8.4 42 1

Nested Loop Left Join (cost=1.69..63.78 rows=5 width=232) (actual time=0.116..1.570 rows=42 loops=1)

14. 0.047 1.151 ↓ 8.4 42 1

Nested Loop Left Join (cost=1.27..61.18 rows=5 width=183) (actual time=0.093..1.151 rows=42 loops=1)

15. 0.052 0.684 ↓ 8.4 42 1

Nested Loop (cost=0.85..58.77 rows=5 width=179) (actual time=0.080..0.684 rows=42 loops=1)

16. 0.296 0.296 ↓ 8.4 42 1

Index Scan using index_sp_account_tracks_on_sp_account_id on sp_account_tracks (cost=0.42..16.57 rows=5 width=5) (actual time=0.060..0.296 rows=42 loops=1)

  • Index Cond: (sp_account_id = 612)
  • Filter: (NOT inactive)
17. 0.336 0.336 ↑ 1.0 1 42

Index Scan using sp_tracks_pkey on sp_tracks (cost=0.42..8.44 rows=1 width=178) (actual time=0.008..0.008 rows=1 loops=42)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
18. 0.420 0.420 ↑ 1.0 1 42

Index Scan using index_sp_album_tracks_on_sp_track_id_unique on sp_album_tracks (cost=0.42..0.48 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=42)

  • Index Cond: (sp_track_id = sp_tracks.id)
19. 0.378 0.378 ↑ 1.0 1 42

Index Scan using sp_albums_pkey on sp_albums (cost=0.42..0.52 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=42)

  • Index Cond: (id = sp_album_tracks.sp_album_id)
20. 1.008 1.008 ↑ 4.0 1 42

Index Scan using index_it_songs_on_isrc on it_songs (cost=0.42..0.55 rows=4 width=51) (actual time=0.023..0.024 rows=1 loops=42)

  • Index Cond: ((isrc)::text = (sp_tracks.isrc)::text)
21. 1.044 1.044 ↓ 0.0 0 58

Index Scan using index_nielsen_songs_on_isrc on nielsen_songs (cost=0.28..0.30 rows=1 width=21) (actual time=0.018..0.018 rows=0 loops=58)

  • Index Cond: (isrc = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
22.          

SubPlan (for Result)

23. 0.000 0.028 ↓ 0.0 0 28

Limit (cost=0.15..8.21 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=28)

24. 0.028 0.028 ↓ 0.0 0 28

Index Scan using ix_nielsen_song_metrics_on_song_id_week_year_metric_type_sorted on nielsen_song_metrics streaming_on_demand_totals (cost=0.15..8.21 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=28)

  • Index Cond: ((nielsen_song_id = window_results.nielsen_song_id) AND (metric_type = 3))
25. 0.028 0.028 ↓ 0.0 0 28

Limit (cost=0.15..8.21 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=28)

26. 0.000 0.000 ↓ 0.0 0 28

Index Scan using ix_nielsen_song_metrics_on_song_id_week_year_metric_type_sorted on nielsen_song_metrics ses_on_demand_totals (cost=0.15..8.21 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=28)

  • Index Cond: ((nielsen_song_id = window_results.nielsen_song_id) AND (metric_type = 0))
Planning time : 5.287 ms
Execution time : 209.412 ms