explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 248Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 109.805 ↓ 28.0 28 1

Limit (cost=21,724.74..21,741.17 rows=1 width=272) (actual time=109.739..109.805 rows=28 loops=1)

2. 0.018 109.801 ↓ 28.0 28 1

Result (cost=21,724.74..21,741.17 rows=1 width=272) (actual time=109.738..109.801 rows=28 loops=1)

3. 0.051 109.727 ↓ 28.0 28 1

Sort (cost=21,724.74..21,724.74 rows=1 width=264) (actual time=109.724..109.727 rows=28 loops=1)

  • Sort Key: window_results.release_date DESC
  • Sort Method: quicksort Memory: 34kB
4. 0.161 109.676 ↓ 28.0 28 1

Subquery Scan on window_results (cost=21,720.67..21,724.73 rows=1 width=264) (actual time=105.786..109.676 rows=28 loops=1)

  • Filter: (window_results.row_number = 1)
  • Rows Removed by Filter: 1,016
5. 3.701 109.515 ↓ 16.1 1,044 1

WindowAgg (cost=21,720.67..21,723.92 rows=65 width=308) (actual time=105.783..109.515 rows=1,044 loops=1)

6. 3.274 105.814 ↓ 16.1 1,044 1

Sort (cost=21,720.67..21,720.83 rows=65 width=330) (actual time=105.722..105.814 rows=1,044 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: 420kB
7. 0.453 102.540 ↓ 16.1 1,044 1

Nested Loop Left Join (cost=2.39..21,718.71 rows=65 width=330) (actual time=3.661..102.540 rows=1,044 loops=1)

  • Join Filter: (sp_account_tracks.sp_account_id = occ_discovery_listings.sp_account_id)
8. 0.083 3.023 ↓ 11.6 58 1

Nested Loop Left Join (cost=2.39..68.21 rows=5 width=282) (actual time=0.136..3.023 rows=58 loops=1)

9. 0.054 1.896 ↓ 11.6 58 1

Nested Loop Left Join (cost=2.11..66.71 rows=5 width=274) (actual time=0.100..1.896 rows=58 loops=1)

10. 0.036 0.876 ↓ 8.4 42 1

Nested Loop Left Join (cost=1.69..63.78 rows=5 width=236) (actual time=0.066..0.876 rows=42 loops=1)

11. 0.047 0.630 ↓ 8.4 42 1

Nested Loop Left Join (cost=1.27..61.18 rows=5 width=187) (actual time=0.053..0.630 rows=42 loops=1)

12. 0.062 0.373 ↓ 8.4 42 1

Nested Loop (cost=0.85..58.77 rows=5 width=183) (actual time=0.044..0.373 rows=42 loops=1)

13. 0.101 0.101 ↓ 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=9) (actual time=0.030..0.101 rows=42 loops=1)

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

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

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

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

  • Index Cond: ((isrc)::text = (sp_tracks.isrc)::text)
18. 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)
19. 0.122 99.064 ↓ 1.4 18 58

Materialize (cost=0.00..21,649.06 rows=13 width=28) (actual time=0.061..1.708 rows=18 loops=58)

20. 98.942 98.942 ↓ 1.4 18 1

Seq Scan on occ_discovery_listings (cost=0.00..21,649.00 rows=13 width=28) (actual time=3.515..98.942 rows=18 loops=1)

  • Filter: (sp_account_id = 612)
  • Rows Removed by Filter: 49,982
21.          

SubPlan (for Result)

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

23. 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 streaming_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 = 3))
24. 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)

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 : 6.277 ms
Execution time : 110.117 ms