explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cIQ2

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

Limit (cost=272,030.68..272,244.30 rows=13 width=268) (actual time=206.536..206.590 rows=28 loops=1)

2. 0.006 206.586 ↓ 2.2 28 1

Result (cost=272,030.68..272,244.30 rows=13 width=268) (actual time=206.535..206.586 rows=28 loops=1)

3. 0.031 206.524 ↓ 2.2 28 1

Sort (cost=272,030.68..272,030.71 rows=13 width=260) (actual time=206.523..206.524 rows=28 loops=1)

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

Subquery Scan on window_results (cost=271,864.25..272,030.44 rows=13 width=260) (actual time=206.268..206.493 rows=28 loops=1)

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

WindowAgg (cost=271,864.25..271,997.20 rows=2,659 width=304) (actual time=206.267..206.480 rows=58 loops=1)

6.          

Initplan (for WindowAgg)

7. 0.323 0.323 ↑ 1.0 1 1

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

  • Filter: (chart_type = 0)
8. 0.227 206.248 ↑ 45.8 58 1

Sort (cost=271,863.24..271,869.89 rows=2,659 width=326) (actual time=206.247..206.248 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. 16.645 206.021 ↑ 45.8 58 1

Hash Right Join (cost=39,185.06..271,711.99 rows=2,659 width=326) (actual time=102.478..206.021 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)
10. 115.678 185.174 ↑ 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=76.249..185.174 rows=70,982 loops=1)

  • Recheck Cond: (occ_chart_source_id = $2)
  • Heap Blocks: exact=24,179
11. 69.496 69.496 ↑ 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=69.496..69.496 rows=70,982 loops=1)

  • Index Cond: (occ_chart_source_id = $2)
12. 0.120 4.202 ↓ 11.6 58 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
13. 0.102 4.082 ↓ 11.6 58 1

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

14. 0.076 2.936 ↓ 11.6 58 1

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

15. 0.050 1.852 ↓ 8.4 42 1

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

16. 0.068 1.382 ↓ 8.4 42 1

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

17. 0.074 0.684 ↓ 8.4 42 1

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

18. 0.274 0.274 ↓ 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.035..0.274 rows=42 loops=1)

  • Index Cond: (sp_account_id = 612)
  • Filter: (NOT inactive)
19. 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)
20. 0.630 0.630 ↑ 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.015..0.015 rows=1 loops=42)

  • Index Cond: (sp_track_id = sp_tracks.id)
21. 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)
22. 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)
23. 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)
24.          

SubPlan (for Result)

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

28. 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.062 ms
Execution time : 206.867 ms