explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1SAj

Settings
# exclusive inclusive rows x rows loops node
1. 9.633 8,130.162 ↓ 5,669.7 17,009 1

Subquery Scan on x (cost=16,816.44..16,836.17 rows=3 width=377) (actual time=7,926.095..8,130.162 rows=17,009 loops=1)

  • Filter: (x.occ_chart_edition_id = 3)
  • Rows Removed by Filter: 16938
2. 105.670 8,120.529 ↓ 64.5 33,947 1

WindowAgg (cost=16,816.44..16,829.59 rows=526 width=390) (actual time=7,926.092..8,120.529 rows=33,947 loops=1)

3. 853.532 8,014.859 ↓ 64.5 33,947 1

Sort (cost=16,816.44..16,817.75 rows=526 width=682) (actual time=7,926.029..8,014.859 rows=33,947 loops=1)

  • Sort Key: occ_chart_listings.primary_isrc, occ_chart_editions.chart_week_ended_on DESC
  • Sort Method: external merge Disk: 13872kB
4. 56.164 7,161.327 ↓ 64.5 33,947 1

Nested Loop Left Join (cost=1,196.98..16,792.67 rows=526 width=682) (actual time=48.809..7,161.327 rows=33,947 loops=1)

5. 66.602 7,071.216 ↓ 64.5 33,947 1

Nested Loop Left Join (cost=1,196.70..16,632.99 rows=526 width=613) (actual time=48.793..7,071.216 rows=33,947 loops=1)

6. 66.957 6,936.720 ↓ 64.5 33,947 1

Nested Loop Left Join (cost=1,196.27..15,814.63 rows=526 width=484) (actual time=48.778..6,936.720 rows=33,947 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 1978
7. 63.701 6,360.558 ↓ 64.5 33,947 1

Nested Loop Left Join (cost=1,195.86..15,497.94 rows=526 width=340) (actual time=48.216..6,360.558 rows=33,947 loops=1)

8. 94.115 6,059.228 ↓ 64.5 33,947 1

Hash Left Join (cost=1,195.57..15,323.29 rows=526 width=336) (actual time=48.210..6,059.228 rows=33,947 loops=1)

  • Hash Cond: ((SubPlan 2) = sp_tracks.id)
9. 4,382.490 4,925.930 ↓ 64.5 33,947 1

Nested Loop (cost=36.38..13,938.57 rows=526 width=216) (actual time=1.744..4,925.930 rows=33,947 loops=1)

10. 170.516 243.440 ↓ 190.1 100,000 1

Hash Join (cost=19.52..4,666.78 rows=526 width=82) (actual time=0.847..243.440 rows=100,000 loops=1)

  • Hash Cond: (occ_chart_listings.occ_chart_edition_id = occ_chart_editions.id)
11. 72.459 72.459 ↑ 1.0 100,000 1

Seq Scan on occ_chart_listings (cost=0.00..4,267.00 rows=100,000 width=78) (actual time=0.373..72.459 rows=100,000 loops=1)

12. 0.003 0.465 ↑ 1.5 2 1

Hash (cost=19.48..19.48 rows=3 width=12) (actual time=0.465..0.465 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.008 0.462 ↑ 1.5 2 1

Nested Loop (cost=4.33..19.48 rows=3 width=12) (actual time=0.460..0.462 rows=2 loops=1)

14. 0.328 0.328 ↑ 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.328..0.328 rows=1 loops=1)

  • Index Cond: (id = 1)
  • Heap Fetches: 1
15. 0.003 0.126 ↑ 1.5 2 1

Bitmap Heap Scan on occ_chart_editions (cost=4.17..11.28 rows=3 width=20) (actual time=0.126..0.126 rows=2 loops=1)

  • Recheck Cond: (occ_chart_source_id = 1)
  • Heap Blocks: exact=1
16. 0.123 0.123 ↑ 1.5 2 1

Bitmap Index Scan on index_occ_chart_editions_on_occ_chart_source_id_and_week_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.123..0.123 rows=2 loops=1)

  • Index Cond: (occ_chart_source_id = 1)
17. 0.000 300.000 ↓ 0.0 0 100,000

Index Scan using it_songs_pkey on it_songs (cost=16.86..17.63 rows=1 width=134) (actual time=0.003..0.003 rows=0 loops=100,000)

  • Index Cond: ((id = (SubPlan 1)) AND (id IS NOT NULL))
18.          

SubPlan (for Index Scan)

19. 100.000 4,100.000 ↓ 0.0 0 100,000

Limit (cost=16.43..16.44 rows=1 width=157) (actual time=0.041..0.041 rows=0 loops=100,000)

20. 500.000 4,000.000 ↓ 0.0 0 100,000

Sort (cost=16.43..16.44 rows=3 width=157) (actual time=0.040..0.040 rows=0 loops=100,000)

  • Sort Key: it_songs_1.preview_url
  • Sort Method: quicksort Memory: 25kB
21. 3,500.000 3,500.000 ↑ 3.0 1 100,000

Index Scan using index_it_songs_on_isrc on it_songs it_songs_1 (cost=0.42..16.42 rows=3 width=157) (actual time=0.030..0.035 rows=1 loops=100,000)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (occ_chart_listings.primary_isrc)::text))
22. 11.874 45.151 ↑ 1.0 20,117 1

Hash (cost=907.42..907.42 rows=20,142 width=120) (actual time=45.151..45.151 rows=20,117 loops=1)

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

Seq Scan on sp_tracks (cost=0.00..907.42 rows=20,142 width=120) (actual time=0.006..33.277 rows=20,117 loops=1)

24.          

SubPlan (for Hash Left Join)

25. 38.232 994.032 ↓ 0.0 0 38,232

Limit (cost=8.32..8.32 rows=1 width=112) (actual time=0.026..0.026 rows=0 loops=38,232)

26. 152.928 955.800 ↓ 0.0 0 38,232

Sort (cost=8.32..8.32 rows=1 width=112) (actual time=0.025..0.025 rows=0 loops=38,232)

  • Sort Key: sp_tracks_1.preview_url
  • Sort Method: quicksort Memory: 25kB
27. 802.872 802.872 ↓ 0.0 0 38,232

Index Scan using index_sp_tracks_on_isrc on sp_tracks sp_tracks_1 (cost=0.29..8.31 rows=1 width=112) (actual time=0.021..0.021 rows=0 loops=38,232)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (occ_chart_listings.primary_isrc)::text))
28. 237.629 237.629 ↑ 1.0 1 33,947

Index Scan using it_accounts_pkey on it_accounts (cost=0.29..0.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=33,947)

  • Index Cond: (it_songs.it_account_id = id)
29. 509.205 509.205 ↑ 1.0 1 33,947

Index Scan using it_collections_pkey on it_collections (cost=0.41..0.59 rows=1 width=156) (actual time=0.015..0.015 rows=1 loops=33,947)

  • Index Cond: (id = it_songs.it_collection_id)
30. 67.894 67.894 ↓ 0.0 0 33,947

Index Scan using artists_pkey on artists (cost=0.42..1.56 rows=1 width=133) (actual time=0.002..0.002 rows=0 loops=33,947)

  • Index Cond: (id = it_accounts.artist_id)
31. 33.947 33.947 ↓ 0.0 0 33,947

Index Scan using index_sp_accounts_on_artist_id on sp_accounts (cost=0.28..0.30 rows=1 width=73) (actual time=0.001..0.001 rows=0 loops=33,947)

  • Index Cond: (artist_id = artists.id)
Planning time : 8.883 ms
Execution time : 8,135.329 ms