explain.depesz.com

PostgreSQL's explain analyze made readable

Result: akCE

Settings
# exclusive inclusive rows x rows loops node
1. 0.498 49.623 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,166.59..5,090.87 rows=105 width=369) (actual time=21.059..49.623 rows=100 loops=1)

2. 0.141 43.025 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,166.30..3,287.12 rows=105 width=608) (actual time=20.986..43.025 rows=100 loops=1)

3. 0.171 41.384 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,165.88..3,123.76 rows=105 width=479) (actual time=20.983..41.384 rows=100 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 7
4. 0.188 37.813 ↑ 1.1 100 1

Nested Loop Left Join (cost=1,165.47..3,060.89 rows=105 width=335) (actual time=20.951..37.813 rows=100 loops=1)

5. 0.425 36.225 ↑ 1.1 100 1

Hash Left Join (cost=1,165.18..3,026.09 rows=105 width=331) (actual time=20.925..36.225 rows=100 loops=1)

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

Nested Loop Left Join (cost=6.55..1,822.53 rows=105 width=211) (actual time=2.557..14.349 rows=100 loops=1)

7. 0.118 2.158 ↑ 1.1 100 1

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

8. 0.003 0.044 ↑ 1.0 1 1

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

9. 0.021 0.021 ↑ 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.020..0.021 rows=1 loops=1)

  • Index Cond: (id = 3)
10. 0.020 0.020 ↑ 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.020..0.020 rows=1 loops=1)

  • Index Cond: (id = occ_chart_editions.occ_chart_source_id)
  • Heap Fetches: 1
11. 1.996 1.996 ↑ 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=1.915..1.996 rows=100 loops=1)

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

Index Scan using it_songs_pkey on it_songs (cost=5.83..13.69 rows=1 width=133) (actual time=0.012..0.012 rows=1 loops=100)

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

SubPlan (for Index Scan)

14. 0.100 10.600 ↑ 1.0 1 100

Limit (cost=0.42..5.41 rows=1 width=4) (actual time=0.106..0.106 rows=1 loops=100)

15. 10.500 10.500 ↑ 4.0 1 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.105..0.105 rows=1 loops=100)

  • Index Cond: ((isrc)::text = (occ_chart_listings.primary_isrc)::text)
16. 6.192 18.091 ↑ 1.0 20,117 1

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

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

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

18.          

SubPlan (for Hash Left Join)

19. 0.140 3.360 ↑ 1.0 1 140

Limit (cost=0.29..8.30 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=140)

20. 3.220 3.220 ↑ 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=4) (actual time=0.023..0.023 rows=1 loops=140)

  • Index Cond: (upper(replace((isrc)::text, '-'::text, ''::text)) = (occ_chart_listings.primary_isrc)::text)
21. 1.400 1.400 ↑ 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.014..0.014 rows=1 loops=100)

  • Index Cond: (it_songs.it_account_id = id)
22. 3.400 3.400 ↑ 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.034..0.034 rows=1 loops=100)

  • Index Cond: (id = it_songs.it_collection_id)
23. 1.500 1.500 ↓ 0.0 0 100

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

  • Index Cond: (id = it_accounts.artist_id)
24. 0.300 0.300 ↓ 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.003..0.003 rows=0 loops=100)

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

SubPlan (for Nested Loop Left Join)

26. 0.100 3.400 ↑ 1.0 1 100

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

27. 3.300 3.300 ↑ 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.033..0.033 rows=1 loops=100)

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

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

29. 2.400 2.400 ↑ 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.024..0.024 rows=1 loops=100)

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