explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wQQG

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 9.416 ↓ 3.1 28 1

Limit (cost=362.81..510.70 rows=9 width=268) (actual time=9.349..9.416 rows=28 loops=1)

2. 0.049 9.410 ↓ 3.1 28 1

Result (cost=362.81..510.70 rows=9 width=268) (actual time=9.348..9.410 rows=28 loops=1)

3. 0.041 9.333 ↓ 3.1 28 1

Sort (cost=362.81..362.83 rows=9 width=260) (actual time=9.330..9.333 rows=28 loops=1)

  • Sort Key: window_results.release_date DESC
  • Sort Method: quicksort Memory: 34kB
4. 0.028 9.292 ↓ 3.1 28 1

Subquery Scan on window_results (cost=251.73..362.67 rows=9 width=260) (actual time=8.994..9.292 rows=28 loops=1)

  • Filter: (window_results.row_number = 1)
  • Rows Removed by Filter: 30
5. 0.000 9.264 ↑ 30.6 58 1

WindowAgg (cost=251.73..340.48 rows=1,775 width=304) (actual time=8.989..9.264 rows=58 loops=1)

6.          

Initplan (for WindowAgg)

7. 0.582 0.582 ↑ 1.0 1 1

Seq Scan on occ_chart_sources (cost=0.00..1.01 rows=1 width=8) (actual time=0.581..0.582 rows=1 loops=1)

  • Filter: (chart_type = 0)
8. 0.400 8.952 ↑ 30.6 58 1

Sort (cost=250.72..255.16 rows=1,775 width=326) (actual time=8.943..8.952 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
9. 0.756 8.552 ↑ 30.6 58 1

Nested Loop Left Join (cost=2.81..154.92 rows=1,775 width=326) (actual time=1.300..8.552 rows=58 loops=1)

10. 0.108 6.346 ↓ 11.6 58 1

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

11. 0.082 5.136 ↓ 11.6 58 1

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

12. 0.060 3.920 ↓ 8.4 42 1

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

13. 0.054 2.264 ↓ 8.4 42 1

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

14. 0.060 0.908 ↓ 8.4 42 1

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

15. 0.386 0.386 ↓ 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.034..0.386 rows=42 loops=1)

  • Index Cond: (sp_account_id = 612)
  • Filter: (NOT inactive)
16. 0.462 0.462 ↑ 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.011..0.011 rows=1 loops=42)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
17. 1.302 1.302 ↑ 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.031..0.031 rows=1 loops=42)

  • Index Cond: (sp_track_id = sp_tracks.id)
18. 1.596 1.596 ↑ 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.038..0.038 rows=1 loops=42)

  • Index Cond: (id = sp_album_tracks.sp_album_id)
19. 1.134 1.134 ↑ 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.026..0.027 rows=1 loops=42)

  • Index Cond: ((isrc)::text = (sp_tracks.isrc)::text)
20. 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)
21. 1.450 1.450 ↑ 355.0 1 58

Index Scan using matt on occ_processed_chart_listings (cost=0.42..11.13 rows=355 width=61) (actual time=0.025..0.025 rows=1 loops=58)

  • Index Cond: (((upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::text = (COALESCE(expected_isrc, primary_isrc))::text) AND (occ_chart_source_id = $2))
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.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)

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 : 23.455 ms
Execution time : 9.666 ms