explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PYWB

Settings
# exclusive inclusive rows x rows loops node
1. 0.374 85.082 ↑ 1.1 100 1

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

2. 0.171 79.808 ↑ 1.1 100 1

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

3. 0.215 79.237 ↑ 1.1 100 1

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

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

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

5. 0.396 77.612 ↑ 1.1 100 1

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

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

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

7. 0.108 0.221 ↑ 1.1 100 1

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

8. 0.003 0.021 ↑ 1.0 1 1

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

9. 0.012 0.012 ↑ 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.010..0.012 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.092 0.092 ↑ 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.092 rows=100 loops=1)

  • Index Cond: ((occ_chart_edition_id = 3) AND ("position" >= 0) AND ("position" <= 100))
12. 0.000 2.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.024..0.024 rows=1 loops=100)

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

SubPlan (for Index Scan)

14. 0.100 57.900 ↑ 1.0 1 100

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

15. 2.700 57.800 ↑ 4.0 1 100

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

  • Sort Key: it_songs_1.id DESC
  • Sort Method: quicksort Memory: 25kB
16. 55.100 55.100 ↓ 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.023..0.551 rows=65 loops=100)

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

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

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

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

19.          

SubPlan (for Hash Left Join)

20. 0.140 3.500 ↑ 1.0 1 140

Limit (cost=8.31..8.32 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=140)

21. 0.700 3.360 ↑ 1.0 1 140

Sort (cost=8.31..8.32 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=140)

  • Sort Key: sp_tracks_1.id DESC
  • Sort Method: quicksort Memory: 25kB
22. 2.660 2.660 ↑ 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.019..0.019 rows=1 loops=140)

  • Index Cond: (upper(replace((isrc)::text, '-'::text, ''::text)) = (occ_chart_listings.primary_isrc)::text)
23. 0.400 0.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.004..0.004 rows=1 loops=100)

  • Index Cond: (it_songs.it_account_id = id)
24. 0.800 0.800 ↑ 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.008..0.008 rows=1 loops=100)

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

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

  • Index Cond: (id = it_accounts.artist_id)
26. 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)
27.          

SubPlan (for Nested Loop Left Join)

28. 0.100 2.500 ↑ 1.0 1 100

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.025..0.025 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 (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))
30. 0.100 2.100 ↑ 1.0 1 100

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

31. 2.000 2.000 ↑ 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.020..0.020 rows=1 loops=100)

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