explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4PlB

Settings
# exclusive inclusive rows x rows loops node
1. 226.492 7,783.589 ↓ 1.0 50,000 1

Hash Left Join (cost=49,856.47..1,951,055.32 rows=49,910 width=373) (actual time=574.800..7,783.589 rows=50,000 loops=1)

  • Hash Cond: (artists.id = sp_accounts.artist_id)
2. 74.811 3,504.367 ↓ 1.0 50,000 1

Hash Left Join (cost=49,489.58..105,477.64 rows=49,910 width=608) (actual time=571.945..3,504.367 rows=50,000 loops=1)

  • Hash Cond: ((SubPlan 5) = sp_tracks.id)
3. 84.188 2,509.975 ↓ 1.0 50,000 1

Hash Left Join (cost=48,330.95..82,956.31 rows=49,910 width=488) (actual time=557.626..2,509.975 rows=50,000 loops=1)

  • Hash Cond: ((it_accounts.id = it_collections.it_account_id) AND (it_songs.it_collection_id = it_collections.id))
4. 22.825 2,306.186 ↓ 1.0 50,000 1

Nested Loop (cost=38,872.15..66,780.38 rows=49,910 width=344) (actual time=437.975..2,306.186 rows=50,000 loops=1)

5. 0.018 0.035 ↑ 1.0 1 1

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

6. 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.007..0.012 rows=1 loops=1)

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

  • Index Cond: (id = occ_chart_editions.occ_chart_source_id)
  • Heap Fetches: 1
8. 57.717 2,283.326 ↓ 1.0 50,000 1

Hash Left Join (cost=38,871.84..66,264.93 rows=49,910 width=344) (actual time=437.960..2,283.326 rows=50,000 loops=1)

  • Hash Cond: (it_songs.it_account_id = it_accounts.id)
9. 177.383 2,096.645 ↓ 1.0 50,000 1

Hash Left Join (cost=25,002.38..48,377.75 rows=49,910 width=211) (actual time=308.948..2,096.645 rows=50,000 loops=1)

  • Hash Cond: ((SubPlan 4) = it_songs.id)
10. 69.280 69.280 ↓ 1.0 50,000 1

Seq Scan on occ_chart_listings (cost=0.00..4,517.00 rows=49,910 width=78) (actual time=0.087..69.280 rows=50,000 loops=1)

  • Filter: (occ_chart_edition_id = 3)
  • Rows Removed by Filter: 50000
11. 146.133 308.775 ↑ 1.0 177,750 1

Hash (cost=19,308.50..19,308.50 rows=177,750 width=133) (actual time=308.775..308.775 rows=177,750 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 3884kB
12. 162.642 162.642 ↑ 1.0 177,750 1

Seq Scan on it_songs (cost=0.00..19,308.50 rows=177,750 width=133) (actual time=0.005..162.642 rows=177,750 loops=1)

13.          

SubPlan (for Hash Left Join)

14. 67.009 1,541.207 ↑ 1.0 1 67,009

Limit (cost=0.42..5.41 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=67,009)

15. 1,474.198 1,474.198 ↑ 4.0 1 67,009

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.022..0.022 rows=1 loops=67,009)

  • Index Cond: ((isrc)::text = (occ_chart_listings.primary_isrc)::text)
16. 9.384 128.964 ↓ 1.1 25,193 1

Hash (cost=13,097.48..13,097.48 rows=23,359 width=137) (actual time=128.964..128.964 rows=25,193 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 751kB
17. 53.570 119.580 ↓ 1.1 25,193 1

Hash Right Join (cost=1,464.58..13,097.48 rows=23,359 width=137) (actual time=13.808..119.580 rows=25,193 loops=1)

  • Hash Cond: (artists.id = it_accounts.artist_id)
18. 52.263 52.263 ↑ 1.0 266,718 1

Seq Scan on artists (cost=0.00..8,240.25 rows=268,725 width=133) (actual time=0.027..52.263 rows=266,718 loops=1)

19. 5.004 13.747 ↓ 1.1 25,193 1

Hash (cost=1,172.59..1,172.59 rows=23,359 width=8) (actual time=13.747..13.747 rows=25,193 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1147kB
20. 8.743 8.743 ↓ 1.1 25,193 1

Seq Scan on it_accounts (cost=0.00..1,172.59 rows=23,359 width=8) (actual time=0.011..8.743 rows=25,193 loops=1)

21. 55.253 119.601 ↓ 1.1 69,606 1

Hash (cost=7,007.32..7,007.32 rows=65,432 width=156) (actual time=119.601..119.601 rows=69,606 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 3480kB
22. 64.348 64.348 ↓ 1.1 69,636 1

Seq Scan on it_collections (cost=0.00..7,007.32 rows=65,432 width=156) (actual time=0.020..64.348 rows=69,636 loops=1)

23. 5.881 14.229 ↑ 1.0 20,117 1

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

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

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

25.          

SubPlan (for Hash Left Join)

26. 53.256 905.352 ↓ 0.0 0 53,256

Limit (cost=0.29..8.30 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=53,256)

27. 852.096 852.096 ↓ 0.0 0 53,256

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.016..0.016 rows=0 loops=53,256)

  • Index Cond: (upper(replace((isrc)::text, '-'::text, ''::text)) = (occ_chart_listings.primary_isrc)::text)
28. 0.650 2.730 ↑ 5.9 1,166 1

Hash (cost=280.84..280.84 rows=6,884 width=73) (actual time=2.730..2.730 rows=1,166 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 180kB
29. 2.080 2.080 ↑ 1.0 6,876 1

Seq Scan on sp_accounts (cost=0.00..280.84 rows=6,884 width=73) (actual time=0.010..2.080 rows=6,876 loops=1)

30.          

SubPlan (for Hash Left Join)

31. 150.000 1,400.000 ↑ 1.0 1 50,000

Limit (cost=4.43..12.31 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=50,000)

32. 200.000 1,250.000 ↑ 1.0 1 50,000

Bitmap Heap Scan on occ_chart_listings ocl (cost=4.43..12.31 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=50,000)

  • Recheck Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
  • Filter: (occ_chart_edition_id = 4)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=50088
33. 1,050.000 1,050.000 ↑ 1.0 2 50,000

Bitmap Index Scan on index_occ_chart_listings_on_isrc (cost=0.00..4.43 rows=2 width=0) (actual time=0.021..0.021 rows=2 loops=50,000)

  • Index Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
34. 200.000 1,400.000 ↑ 1.0 1 50,000

Aggregate (cost=12.32..12.33 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=50,000)

35. 200.000 1,200.000 ↑ 1.0 2 50,000

Bitmap Heap Scan on occ_chart_listings ocl_1 (cost=4.43..12.31 rows=2 width=0) (actual time=0.023..0.024 rows=2 loops=50,000)

  • Recheck Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
  • Filter: (occ_chart_edition_id = ANY ('{3,4}'::bigint[]))
  • Heap Blocks: exact=97949
36. 1,000.000 1,000.000 ↑ 1.0 2 50,000

Bitmap Index Scan on index_occ_chart_listings_on_isrc (cost=0.00..4.43 rows=2 width=0) (actual time=0.020..0.020 rows=2 loops=50,000)

  • Index Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
37. 150.000 1,250.000 ↑ 1.0 1 50,000

Limit (cost=4.43..12.31 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=50,000)

38. 150.000 1,100.000 ↑ 1.0 1 50,000

Bitmap Heap Scan on occ_chart_listings ocl_2 (cost=4.43..12.31 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=50,000)

  • Recheck Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
  • Filter: (occ_chart_edition_id = 4)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=50088
39. 950.000 950.000 ↑ 1.0 2 50,000

Bitmap Index Scan on index_occ_chart_listings_on_isrc (cost=0.00..4.43 rows=2 width=0) (actual time=0.019..0.019 rows=2 loops=50,000)

  • Index Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
Planning time : 2.537 ms
Execution time : 7,791.182 ms