explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5RbB

Settings
# exclusive inclusive rows x rows loops node
1. 0.318 39.648 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,181.56..6,663.60 rows=105 width=369) (actual time=18.572..39.648 rows=100 loops=1)

2. 0.135 35.030 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,181.28..4,859.85 rows=105 width=608) (actual time=18.500..35.030 rows=100 loops=1)

3. 0.114 34.695 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,180.86..4,696.49 rows=105 width=479) (actual time=18.497..34.695 rows=100 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 8
4. 0.128 34.281 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,180.44..4,633.62 rows=105 width=335) (actual time=18.486..34.281 rows=100 loops=1)

5. 0.308 33.853 ↑ 1.1 100 1

Hash Left Join (cost=1,180.16..4,598.81 rows=105 width=331) (actual time=18.475..33.853 rows=100 loops=1)

  • Hash Cond: ((SubPlan 4) = sp_tracks.id)
6. 12.297 12.864 ↑ 1.1 100 1

Nested Loop Left Join (cost=21.52..3,395.18 rows=105 width=211) (actual time=0.337..12.864 rows=100 loops=1)

7. 0.081 0.167 ↑ 1.1 100 1

Nested Loop (cost=0.72..385.06 rows=105 width=78) (actual time=0.039..0.167 rows=100 loops=1)

8. 0.002 0.017 ↑ 1.0 1 1

Nested Loop (cost=0.30..16.35 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)

9. 0.009 0.009 ↑ 1.0 1 1

Index Scan using occ_chart_editions_pkey on occ_chart_editions (cost=0.15..8.17 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (id = 3)
10. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using occ_chart_sources_pkey on occ_chart_sources (cost=0.15..8.17 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = occ_chart_editions.occ_chart_source_id)
  • Heap Fetches: 1
11. 0.069 0.069 ↑ 1.1 100 1

Index Scan using index_occ_chart_listings_on_edition_id_and_position on occ_chart_listings (cost=0.42..367.66 rows=105 width=78) (actual time=0.022..0.069 rows=100 loops=1)

  • Index Cond: ((occ_chart_edition_id = 3) AND ("position" >= 0) AND ("position" <= 100))
12. 0.000 0.400 ↑ 1.0 1 100

Index Scan using it_songs_pkey on it_songs (cost=20.80..28.67 rows=1 width=133) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (id = (SubPlan 3))
13.          

SubPlan (for Index Scan)

14. 0.100 12.100 ↑ 1.0 1 100

Limit (cost=20.38..20.38 rows=1 width=4) (actual time=0.121..0.121 rows=1 loops=100)

15. 1.800 12.000 ↑ 4.0 1 100

Sort (cost=20.38..20.39 rows=4 width=4) (actual time=0.120..0.120 rows=1 loops=100)

  • Sort Key: it_songs_1.id DESC
  • Sort Method: quicksort Memory: 25kB
16. 10.200 10.200 ↓ 16.2 65 100

Index Scan using index_it_songs_on_isrc on it_songs it_songs_1 (cost=0.42..20.36 rows=4 width=4) (actual time=0.027..0.102 rows=65 loops=100)

  • Index Cond: ((isrc)::text = (occ_chart_listings.primary_isrc)::text)
17. 7.401 18.021 ↑ 1.0 20,117 1

Hash (cost=907.17..907.17 rows=20,117 width=120) (actual time=18.021..18.021 rows=20,117 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2764kB
18. 10.620 10.620 ↑ 1.0 20,117 1

Seq Scan on sp_tracks (cost=0.00..907.17 rows=20,117 width=120) (actual time=0.008..10.620 rows=20,117 loops=1)

19.          

SubPlan (for Hash Left Join)

20. 0.000 2.660 ↑ 1.0 1 140

Limit (cost=8.31..8.32 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=140)

21. 0.420 2.660 ↑ 1.0 1 140

Sort (cost=8.31..8.32 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=140)

  • Sort Key: sp_tracks_1.popularity DESC
  • Sort Method: quicksort Memory: 25kB
22. 2.240 2.240 ↑ 1.0 1 140

Index Scan using index_sp_tracks_on_sanitized_isrc on sp_tracks sp_tracks_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=140)

  • Index Cond: (upper(replace((isrc)::text, '-'::text, ''::text)) = (occ_chart_listings.primary_isrc)::text)
  • Filter: (isrc IS NOT NULL)
23. 0.300 0.300 ↑ 1.0 1 100

Index Scan using it_accounts_pkey on it_accounts (cost=0.29..0.33 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (it_songs.it_account_id = id)
24. 0.300 0.300 ↑ 1.0 1 100

Index Scan using it_collections_pkey on it_collections (cost=0.41..0.59 rows=1 width=156) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (id = it_songs.it_collection_id)
25. 0.200 0.200 ↓ 0.0 0 100

Index Scan using artists_pkey on artists (cost=0.42..1.56 rows=1 width=133) (actual time=0.002..0.002 rows=0 loops=100)

  • Index Cond: (id = it_accounts.artist_id)
26. 0.200 0.200 ↓ 0.0 0 100

Index Scan using index_sp_accounts_on_artist_id on sp_accounts (cost=0.28..0.30 rows=1 width=73) (actual time=0.002..0.002 rows=0 loops=100)

  • Index Cond: (artist_id = artists.id)
27.          

SubPlan (for Nested Loop Left Join)

28. 0.100 2.200 ↑ 1.0 1 100

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=100)

29. 2.100 2.100 ↑ 1.0 1 100

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl (cost=0.42..8.44 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=100)

  • Index Cond: ((occ_chart_edition_id = 4) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
30. 0.000 1.900 ↑ 1.0 1 100

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=100)

31. 1.900 1.900 ↑ 1.0 1 100

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=100)

  • Index Cond: ((occ_chart_edition_id = 4) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
Planning time : 3.447 ms
Execution time : 39.904 ms