explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nUQX

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 6,528.492 ↑ 7.1 28 1

Limit (cost=272,885.46..276,171.96 rows=200 width=268) (actual time=6,528.417..6,528.492 rows=28 loops=1)

2. 0.055 6,528.489 ↑ 19.0 28 1

Result (cost=272,885.46..281,627.55 rows=532 width=268) (actual time=6,528.416..6,528.489 rows=28 loops=1)

3. 0.153 6,528.406 ↑ 19.0 28 1

Sort (cost=272,885.46..272,886.79 rows=532 width=260) (actual time=6,528.401..6,528.406 rows=28 loops=1)

  • Sort Key: window_results.release_date DESC
  • Sort Method: quicksort Memory: 34kB
4. 1,720.354 6,528.253 ↑ 19.0 28 1

Nested Loop Left Join (cost=39,185.09..272,862.46 rows=532 width=260) (actual time=252.088..6,528.253 rows=28 loops=1)

  • Join Filter: ((upper(replace((window_results.isrc)::text, '-'::text, ''::text)))::text = (COALESCE(occ_processed_chart_listings.expected_isrc, occ_processed_chart_listings.primary_isrc))::text)
  • Rows Removed by Join Filter: 1,987,476
5. 0.042 7.943 ↓ 28.0 28 1

Subquery Scan on window_results (cost=68.31..68.62 rows=1 width=244) (actual time=7.240..7.943 rows=28 loops=1)

  • Filter: (window_results.row_number = 1)
  • Rows Removed by Filter: 30
6. 0.655 7.901 ↓ 11.6 58 1

WindowAgg (cost=68.31..68.56 rows=5 width=288) (actual time=7.238..7.901 rows=58 loops=1)

7. 0.322 7.246 ↓ 11.6 58 1

Sort (cost=68.31..68.32 rows=5 width=310) (actual time=7.221..7.246 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
8. 0.140 6.924 ↓ 11.6 58 1

Nested Loop Left Join (cost=2.39..68.25 rows=5 width=310) (actual time=0.713..6.924 rows=58 loops=1)

9. 0.051 4.986 ↓ 11.6 58 1

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

10. 0.058 3.885 ↓ 8.4 42 1

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

11. 0.065 2.063 ↓ 8.4 42 1

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

12. 0.066 0.906 ↓ 8.4 42 1

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

13. 0.252 0.252 ↓ 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.031..0.252 rows=42 loops=1)

  • Index Cond: (sp_account_id = 612)
  • Filter: (NOT inactive)
14. 0.588 0.588 ↑ 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.014..0.014 rows=1 loops=42)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
15. 1.092 1.092 ↑ 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.026..0.026 rows=1 loops=42)

  • Index Cond: (sp_track_id = sp_tracks.id)
16. 1.764 1.764 ↑ 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.042..0.042 rows=1 loops=42)

  • Index Cond: (id = sp_album_tracks.sp_album_id)
17. 1.050 1.050 ↑ 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.024..0.025 rows=1 loops=42)

  • Index Cond: ((isrc)::text = (sp_tracks.isrc)::text)
18. 1.798 1.798 ↓ 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.031..0.031 rows=0 loops=58)

  • Index Cond: (isrc = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
19. 3,227.560 4,799.956 ↑ 1.5 70,982 28

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

  • Recheck Cond: (occ_chart_source_id = 1)
  • Heap Blocks: exact=677,012
20. 1,572.396 1,572.396 ↑ 1.5 70,982 28

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=56.157..56.157 rows=70,982 loops=28)

  • Index Cond: (occ_chart_source_id = 1)
21.          

SubPlan (for Result)

22. 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)

23. 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))
24. 0.000 0.000 ↓ 0.0 0 28

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

25. 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 : 8.362 ms
Execution time : 6,528.748 ms