explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NpYD

Settings
# exclusive inclusive rows x rows loops node
1. 1.370 738,825.150 ↓ 1.2 100 1

Nested Loop Left Join (cost=991,262.53..81,286,419.63 rows=82 width=363) (actual time=11,384.607..738,825.150 rows=100 loops=1)

2. 0.446 738,760.280 ↓ 1.2 100 1

Nested Loop Left Join (cost=991,262.10..81,284,963.57 rows=82 width=597) (actual time=11,381.705..738,760.280 rows=100 loops=1)

3. 0.668 738,757.634 ↓ 1.2 100 1

Nested Loop Left Join (cost=991,261.67..81,284,906.43 rows=82 width=479) (actual time=11,381.688..738,757.634 rows=100 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 4
4. 0.518 738,581.666 ↓ 1.2 100 1

Nested Loop Left Join (cost=991,261.24..81,284,861.10 rows=82 width=333) (actual time=11,381.652..738,581.666 rows=100 loops=1)

5. 738,509.077 738,570.548 ↓ 1.2 100 1

Nested Loop Left Join (cost=991,260.82..81,284,823.12 rows=82 width=329) (actual time=11,381.136..738,570.548 rows=100 loops=1)

6. 8.649 55.471 ↓ 1.2 100 1

Nested Loop Left Join (cost=7.46..1,389.35 rows=82 width=209) (actual time=1.633..55.471 rows=100 loops=1)

7. 0.235 3.322 ↓ 1.2 100 1

Nested Loop (cost=0.57..166.57 rows=82 width=78) (actual time=1.568..3.322 rows=100 loops=1)

8. 0.008 0.741 ↑ 1.0 1 1

Nested Loop (cost=0.14..9.18 rows=1 width=8) (actual time=0.737..0.741 rows=1 loops=1)

  • Join Filter: (occ_chart_editions.occ_chart_source_id = occ_chart_sources.id)
9. 0.041 0.041 ↑ 1.0 1 1

Index Scan using occ_chart_editions_pkey on occ_chart_editions (cost=0.14..8.16 rows=1 width=16) (actual time=0.040..0.041 rows=1 loops=1)

  • Index Cond: (id = 100)
10. 0.692 0.692 ↑ 1.0 1 1

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

11. 2.346 2.346 ↓ 1.2 100 1

Index Scan using index_occ_chart_listings_on_edition_id_and_position on occ_chart_listings (cost=0.43..156.56 rows=82 width=78) (actual time=0.828..2.346 rows=100 loops=1)

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

Index Scan using it_songs_pkey on it_songs (cost=6.88..14.90 rows=1 width=131) (actual time=0.386..0.435 rows=1 loops=100)

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

SubPlan (for Index Scan)

14. 0.300 7.900 ↑ 1.0 1 100

Limit (cost=0.43..6.45 rows=1 width=4) (actual time=0.078..0.079 rows=1 loops=100)

15. 7.600 7.600 ↑ 2.0 1 100

Index Scan using index_it_songs_on_isrc on it_songs it_songs_1 (cost=0.43..12.46 rows=2 width=4) (actual time=0.076..0.076 rows=1 loops=100)

  • Index Cond: ((isrc)::text = (occ_chart_listings.primary_isrc)::text)
16. 0.000 6.000 ↑ 1.0 1 100

Index Scan using sp_tracks_pkey on sp_tracks (cost=991,253.36..991,261.38 rows=1 width=120) (actual time=0.055..0.060 rows=1 loops=100)

  • Index Cond: (id = (SubPlan 4))
17.          

SubPlan (for Index Scan)

18. 0.500 738,508.000 ↑ 1.0 1 100

Limit (cost=0.00..991,252.93 rows=1 width=4) (actual time=7,385.079..7,385.080 rows=1 loops=100)

19. 738,507.500 738,507.500 ↑ 1.0 1 100

Seq Scan on sp_tracks sp_tracks_1 (cost=0.00..991,252.93 rows=1 width=4) (actual time=7,385.075..7,385.075 rows=1 loops=100)

  • Filter: ((upper(replace((isrc)::text, '-'::text, ''::text)))::text = (occ_chart_listings.primary_isrc)::text)
  • Rows Removed by Filter: 7727902
20. 10.600 10.600 ↑ 1.0 1 100

Index Scan using it_accounts_pkey on it_accounts (cost=0.42..0.45 rows=1 width=8) (actual time=0.105..0.106 rows=1 loops=100)

  • Index Cond: (it_songs.it_account_id = id)
21. 175.300 175.300 ↑ 1.0 1 100

Index Scan using it_collections_pkey on it_collections (cost=0.43..0.54 rows=1 width=158) (actual time=1.749..1.753 rows=1 loops=100)

  • Index Cond: (id = it_songs.it_collection_id)
22. 2.200 2.200 ↑ 1.0 1 100

Index Scan using artists_pkey on artists (cost=0.42..0.69 rows=1 width=122) (actual time=0.021..0.022 rows=1 loops=100)

  • Index Cond: (id = it_accounts.artist_id)
23. 8.700 8.700 ↑ 1.0 1 100

Index Scan using index_sp_accounts_on_artist_id on sp_accounts (cost=0.43..0.84 rows=1 width=73) (actual time=0.085..0.087 rows=1 loops=100)

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

SubPlan (for Nested Loop Left Join)

25. 0.300 53.100 ↑ 1.0 1 100

Limit (cost=0.43..8.45 rows=1 width=4) (actual time=0.531..0.531 rows=1 loops=100)

26. 52.800 52.800 ↑ 1.0 1 100

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl (cost=0.43..8.45 rows=1 width=4) (actual time=0.528..0.528 rows=1 loops=100)

  • Index Cond: ((occ_chart_edition_id = 99) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
27. 0.300 1.700 ↑ 1.0 1 100

Limit (cost=0.43..8.45 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=100)

28. 1.400 1.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.43..8.45 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=100)

  • Index Cond: ((occ_chart_edition_id = 99) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
Planning time : 22.518 ms
Execution time : 738,825.490 ms