explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BHNO

Settings
# exclusive inclusive rows x rows loops node
1. 7.360 2,114.316 ↑ 1.2 1,001 1

Nested Loop Left Join (cost=24.22..68,346.78 rows=1,153 width=363) (actual time=1.644..2,114.316 rows=1,001 loops=1)

2. 2.427 2,030.880 ↑ 1.2 1,001 1

Nested Loop Left Join (cost=23.79..47,873.05 rows=1,153 width=597) (actual time=1.586..2,030.880 rows=1,001 loops=1)

3. 3.146 2,018.443 ↑ 1.2 1,001 1

Nested Loop Left Join (cost=23.37..47,069.70 rows=1,153 width=479) (actual time=1.577..2,018.443 rows=1,001 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 116
4. 2.578 1,737.019 ↑ 1.2 1,001 1

Nested Loop Left Join (cost=22.94..46,432.24 rows=1,153 width=333) (actual time=1.550..1,737.019 rows=1,001 loops=1)

5. 1,136.612 1,702.409 ↑ 1.2 1,001 1

Nested Loop Left Join (cost=22.51..45,898.29 rows=1,153 width=329) (actual time=1.542..1,702.409 rows=1,001 loops=1)

6. 169.607 525.757 ↑ 1.2 1,001 1

Nested Loop Left Join (cost=13.49..26,257.88 rows=1,153 width=209) (actual time=0.724..525.757 rows=1,001 loops=1)

7. 1.398 5.800 ↑ 1.2 1,001 1

Nested Loop (cost=0.57..2,151.21 rows=1,153 width=78) (actual time=0.657..5.800 rows=1,001 loops=1)

8. 0.007 0.039 ↑ 1.0 1 1

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

  • Join Filter: (occ_chart_editions.occ_chart_source_id = occ_chart_sources.id)
9. 0.027 0.027 ↑ 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.026..0.027 rows=1 loops=1)

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

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

11. 4.363 4.363 ↑ 1.2 1,001 1

Index Scan using index_occ_chart_listings_on_edition_id_and_position on occ_chart_listings (cost=0.43..2,130.49 rows=1,153 width=78) (actual time=0.620..4.363 rows=1,001 loops=1)

  • Index Cond: ((occ_chart_edition_id = 100) AND ("position" >= 5000) AND ("position" <= 6000))
12. 185.185 350.350 ↑ 1.0 1 1,001

Index Scan using it_songs_pkey on it_songs (cost=12.91..20.90 rows=1 width=131) (actual time=0.343..0.350 rows=1 loops=1,001)

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

SubPlan (for Index Scan)

14. 2.002 165.165 ↑ 1.0 1 1,001

Limit (cost=12.47..12.48 rows=1 width=4) (actual time=0.165..0.165 rows=1 loops=1,001)

15. 7.007 163.163 ↑ 2.0 1 1,001

Sort (cost=12.47..12.48 rows=2 width=4) (actual time=0.163..0.163 rows=1 loops=1,001)

  • Sort Key: it_songs_1.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
16. 156.156 156.156 ↓ 2.0 4 1,001

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.146..0.156 rows=4 loops=1,001)

  • Index Cond: ((isrc)::text = (occ_chart_listings.primary_isrc)::text)
17. 0.000 40.040 ↑ 1.0 1 1,001

Index Scan using sp_tracks_pkey on sp_tracks (cost=9.03..17.02 rows=1 width=120) (actual time=0.039..0.040 rows=1 loops=1,001)

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

SubPlan (for Index Scan)

19. 2.002 1,132.131 ↑ 1.0 1 1,001

Limit (cost=8.59..8.59 rows=1 width=4) (actual time=1.131..1.131 rows=1 loops=1,001)

20. 7.007 1,130.129 ↑ 1.0 1 1,001

Sort (cost=8.59..8.59 rows=1 width=4) (actual time=1.129..1.129 rows=1 loops=1,001)

  • Sort Key: sp_tracks_1.id DESC
  • Sort Method: quicksort Memory: 25kB
21. 1,123.122 1,123.122 ↓ 4.0 4 1,001

Index Scan using index_sp_tracks_on_sanitized_isrc on sp_tracks sp_tracks_1 (cost=0.56..8.58 rows=1 width=4) (actual time=1.092..1.122 rows=4 loops=1,001)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (occ_chart_listings.primary_isrc)::citext)
  • Filter: (isrc IS NOT NULL)
22. 32.032 32.032 ↑ 1.0 1 1,001

Index Scan using it_accounts_pkey on it_accounts (cost=0.42..0.45 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=1,001)

  • Index Cond: (it_songs.it_account_id = id)
23. 278.278 278.278 ↑ 1.0 1 1,001

Index Scan using it_collections_pkey on it_collections (cost=0.43..0.54 rows=1 width=158) (actual time=0.276..0.278 rows=1 loops=1,001)

  • Index Cond: (id = it_songs.it_collection_id)
24. 10.010 10.010 ↑ 1.0 1 1,001

Index Scan using artists_pkey on artists (cost=0.42..0.69 rows=1 width=122) (actual time=0.009..0.010 rows=1 loops=1,001)

  • Index Cond: (id = it_accounts.artist_id)
25. 23.023 23.023 ↑ 1.0 1 1,001

Index Scan using index_sp_accounts_on_artist_id on sp_accounts (cost=0.43..0.84 rows=1 width=73) (actual time=0.022..0.023 rows=1 loops=1,001)

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

SubPlan (for Nested Loop Left Join)

27. 2.002 41.041 ↑ 1.0 1 1,001

Limit (cost=0.43..8.45 rows=1 width=4) (actual time=0.041..0.041 rows=1 loops=1,001)

28. 39.039 39.039 ↑ 1.0 1 1,001

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.039..0.039 rows=1 loops=1,001)

  • Index Cond: ((occ_chart_edition_id = 99) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
29. 1.001 12.012 ↑ 1.0 1 1,001

Limit (cost=0.43..8.45 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=1,001)

30. 11.011 11.011 ↑ 1.0 1 1,001

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.011..0.011 rows=1 loops=1,001)

  • Index Cond: ((occ_chart_edition_id = 99) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
Planning time : 4.990 ms
Execution time : 2,115.188 ms