explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7eZD

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 8.751 ↓ 28.0 28 1

Limit (cost=68.63..85.07 rows=1 width=252) (actual time=8.684..8.751 rows=28 loops=1)

2. 0.028 8.745 ↓ 28.0 28 1

Result (cost=68.63..85.07 rows=1 width=252) (actual time=8.683..8.745 rows=28 loops=1)

3. 0.079 8.661 ↓ 28.0 28 1

Sort (cost=68.63..68.64 rows=1 width=244) (actual time=8.661..8.661 rows=28 loops=1)

  • Sort Key: window_results.release_date DESC
  • Sort Method: quicksort Memory: 33kB
4. 0.026 8.582 ↓ 28.0 28 1

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

  • Filter: (window_results.row_number = 1)
  • Rows Removed by Filter: 30
5. 0.295 8.556 ↓ 11.6 58 1

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

6. 4.238 8.261 ↓ 11.6 58 1

Sort (cost=68.31..68.32 rows=5 width=310) (actual time=8.255..8.261 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. 0.148 4.023 ↓ 11.6 58 1

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

8. 0.090 2.773 ↓ 11.6 58 1

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

9. 0.047 1.675 ↓ 8.4 42 1

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

10. 0.054 1.208 ↓ 8.4 42 1

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

11. 0.037 0.692 ↓ 8.4 42 1

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

12. 0.235 0.235 ↓ 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.041..0.235 rows=42 loops=1)

  • Index Cond: (sp_account_id = 612)
  • Filter: (NOT inactive)
13. 0.420 0.420 ↑ 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.010..0.010 rows=1 loops=42)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
14. 0.462 0.462 ↑ 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.011..0.011 rows=1 loops=42)

  • Index Cond: (sp_track_id = sp_tracks.id)
15. 0.420 0.420 ↑ 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.010..0.010 rows=1 loops=42)

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

  • Index Cond: ((isrc)::text = (sp_tracks.isrc)::text)
17. 1.102 1.102 ↓ 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.019..0.019 rows=0 loops=58)

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

SubPlan (for Result)

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

20. 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))
21. 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)

22. 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 : 11.238 ms
Execution time : 10.686 ms