explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OeDt

Settings
# exclusive inclusive rows x rows loops node
1. 29.468 250,979.937 ↑ 1.5 999 1

Nested Loop Left Join (cost=10.47..19,303,804.70 rows=1,498 width=1,014) (actual time=282.474..250,979.937 rows=999 loops=1)

2. 5.082 958.711 ↑ 1.5 999 1

Nested Loop Left Join (cost=10.04..24,278.22 rows=1,498 width=1,404) (actual time=3.706..958.711 rows=999 loops=1)

3. 4.614 880.702 ↑ 1.5 999 1

Nested Loop Left Join (cost=9.61..23,522.86 rows=1,498 width=1,209) (actual time=3.692..880.702 rows=999 loops=1)

4. 6.449 761.203 ↑ 1.5 999 1

Nested Loop Left Join (cost=9.18..22,641.47 rows=1,498 width=1,205) (actual time=3.338..761.203 rows=999 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 204
5. 5.142 692.816 ↑ 1.5 999 1

Nested Loop Left Join (cost=8.75..21,917.15 rows=1,498 width=1,059) (actual time=3.311..692.816 rows=999 loops=1)

6. 163.548 672.689 ↑ 1.5 999 1

Nested Loop Left Join (cost=8.32..21,223.50 rows=1,498 width=1,055) (actual time=3.303..672.689 rows=999 loops=1)

7. 305.865 490.160 ↑ 1.5 999 1

Nested Loop Left Join (cost=5.18..13,484.84 rows=1,498 width=922) (actual time=3.229..490.160 rows=999 loops=1)

8. 6.537 103.376 ↑ 1.5 999 1

Nested Loop (cost=1.12..4,390.35 rows=1,498 width=697) (actual time=3.153..103.376 rows=999 loops=1)

  • Join Filter: (occ_chart_sources.id = occ_processed_chart_listings.occ_chart_source_id)
9. 3.231 26.909 ↑ 1.5 999 1

Nested Loop (cost=0.71..1,561.25 rows=1,498 width=112) (actual time=3.124..26.909 rows=999 loops=1)

10. 0.009 0.036 ↑ 1.0 1 1

Nested Loop (cost=0.14..3.19 rows=1 width=24) (actual time=0.030..0.036 rows=1 loops=1)

  • Join Filter: (occ_chart_editions.occ_chart_source_id = occ_chart_sources.id)
11. 0.016 0.016 ↑ 1.0 1 1

Index Scan using occ_chart_editions_pkey on occ_chart_editions (cost=0.14..2.16 rows=1 width=16) (actual time=0.014..0.016 rows=1 loops=1)

  • Index Cond: (id = 133)
12. 0.011 0.011 ↑ 1.0 1 1

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

13. 23.642 23.642 ↑ 1.5 999 1

Index Scan using index_occ_chart_listings_on_edition_id_and_position on occ_chart_listings (cost=0.56..1,543.09 rows=1,498 width=96) (actual time=3.091..23.642 rows=999 loops=1)

  • Index Cond: ((occ_chart_edition_id = 133) AND ("position" >= 0) AND ("position" <= 999))
14. 69.930 69.930 ↑ 1.0 1 999

Index Scan using index_occ_processed_chart_listings_on_pri_isrc_and_chart_source on occ_processed_chart_listings (cost=0.42..1.88 rows=1 width=622) (actual time=0.068..0.070 rows=1 loops=999)

  • Index Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
15. 0.000 80.919 ↑ 1.0 1 999

Index Scan using it_songs_pkey on it_songs (cost=4.05..6.06 rows=1 width=225) (actual time=0.076..0.081 rows=1 loops=999)

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

SubPlan (for Index Scan)

17. 3.996 295.704 ↑ 1.0 1 999

Limit (cost=3.61..3.62 rows=1 width=8) (actual time=0.296..0.296 rows=1 loops=999)

18. 14.985 291.708 ↑ 2.0 1 999

Sort (cost=3.61..3.62 rows=2 width=8) (actual time=0.292..0.292 rows=1 loops=999)

  • Sort Key: (char_length((it_songs_2.composer_names)::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
19. 276.723 276.723 ↓ 4.0 8 999

Index Scan using index_it_songs_on_isrc on it_songs it_songs_2 (cost=0.56..3.60 rows=2 width=8) (actual time=0.068..0.277 rows=8 loops=999)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::text))
20. 0.000 18.981 ↑ 1.0 1 999

Index Scan using sp_tracks_pkey on sp_tracks (cost=3.14..5.16 rows=1 width=133) (actual time=0.016..0.019 rows=1 loops=999)

  • Index Cond: (id = (SubPlan 5))
21.          

SubPlan (for Index Scan)

22. 2.997 154.845 ↑ 1.0 1 999

Limit (cost=0.56..2.58 rows=1 width=8) (actual time=0.155..0.155 rows=1 loops=999)

23. 151.848 151.848 ↑ 1.0 1 999

Index Scan using index_sp_tracks_on_sanitized_isrc_and_popularity_desc on sp_tracks sp_tracks_2 (cost=0.56..2.58 rows=1 width=8) (actual time=0.152..0.152 rows=1 loops=999)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::citext)
24. 14.985 14.985 ↑ 1.0 1 999

Index Scan using it_accounts_pkey on it_accounts (cost=0.43..0.45 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=999)

  • Index Cond: (it_songs.it_account_id = id)
25. 61.938 61.938 ↑ 1.0 1 999

Index Scan using it_collections_pkey on it_collections (cost=0.43..0.47 rows=1 width=158) (actual time=0.057..0.062 rows=1 loops=999)

  • Index Cond: (id = it_songs.it_collection_id)
26. 114.885 114.885 ↑ 5.0 1 999

Index Scan using index_sp_account_tracks_on_sp_track_id_and_primary on sp_account_tracks (cost=0.44..0.54 rows=5 width=8) (actual time=0.114..0.115 rows=1 loops=999)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Filter: (NOT inactive)
27. 72.927 72.927 ↑ 1.0 1 999

Index Scan using sp_accounts_pkey on sp_accounts (cost=0.43..0.49 rows=1 width=199) (actual time=0.022..0.073 rows=1 loops=999)

  • Index Cond: (id = sp_account_tracks.sp_account_id)
28. 24.975 24.975 ↑ 1.0 1 999

Index Scan using artists_pkey on artists (cost=0.43..0.55 rows=1 width=154) (actual time=0.024..0.025 rows=1 loops=999)

  • Index Cond: (id = it_accounts.artist_id)
29.          

SubPlan (for Nested Loop Left Join)

30. 1,132.866 8,398.593 ↑ 1.0 1 999

Aggregate (cost=261.56..261.57 rows=1 width=8) (actual time=8.407..8.407 rows=1 loops=999)

31. 647.662 7,265.727 ↓ 5.7 362 999

Nested Loop (cost=1.00..261.40 rows=64 width=36) (actual time=0.088..7.273 rows=362 loops=999)

32. 1,549.449 1,549.449 ↓ 5.7 362 999

Index Scan using index_sp_account_tracks_on_sp_account_id_and_sp_track_id on sp_account_tracks sp_account_tracks_1 (cost=0.44..95.64 rows=64 width=4) (actual time=0.061..1.551 rows=362 loops=999)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 94
33. 5,068.616 5,068.616 ↑ 1.0 1 362,044

Index Scan using sp_tracks_pkey on sp_tracks sp_tracks_1 (cost=0.56..2.58 rows=1 width=40) (actual time=0.014..0.014 rows=1 loops=362,044)

  • Index Cond: (id = sp_account_tracks_1.sp_track_id)
34. 1,664.334 7,243.749 ↑ 1.0 1 999

Aggregate (cost=150.49..150.50 rows=1 width=8) (actual time=7.251..7.251 rows=1 loops=999)

35. 5,579.415 5,579.415 ↓ 3.0 448 999

Index Scan using index_it_songs_on_it_account_id on it_songs it_songs_1 (cost=0.43..149.01 rows=148 width=17) (actual time=0.053..5.585 rows=448 loops=999)

  • Index Cond: (it_account_id = it_accounts.id)
36. 18.981 234,324.441 ↑ 1.0 1 999

Aggregate (cost=12,457.53..12,457.54 rows=1 width=8) (actual time=234.559..234.559 rows=1 loops=999)

37. 70.929 234,305.460 ↓ 7.2 36 999

HashAggregate (cost=12,457.42..12,457.47 rows=5 width=8) (actual time=234.525..234.540 rows=36 loops=999)

  • Group Key: sp_playlists.id, sp_playlists.followers_count
38. 39.960 234,234.531 ↓ 12.4 62 999

Append (cost=1.30..12,457.39 rows=5 width=8) (actual time=0.874..234.469 rows=62 loops=999)

39. 47.343 93,989.916 ↓ 6.2 25 999

Nested Loop (cost=1.30..9,714.60 rows=4 width=8) (actual time=0.724..94.084 rows=25 loops=999)

40. 6,725.784 93,841.065 ↓ 6.2 25 999

Nested Loop (cost=1.01..9,712.31 rows=4 width=4) (actual time=0.714..93.935 rows=25 loops=999)

41. 39,827.133 39,827.133 ↓ 4.1 11,834 999

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings (cost=0.58..2,683.92 rows=2,883 width=4) (actual time=0.090..39.867 rows=11,834 loops=999)

  • Index Cond: (sp_track_id = sp_tracks.id)
42. 47,288.148 47,288.148 ↓ 0.0 0 11,822,037

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.43..2.43 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=11,822,037)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 1
43. 101.508 101.508 ↑ 1.0 1 25,377

Index Scan using sp_playlists_pkey on sp_playlists (cost=0.29..0.56 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=25,377)

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
44. 70.497 140,204.655 ↓ 36.0 36 999

Nested Loop (cost=1.87..2,742.74 rows=1 width=8) (actual time=0.609..140.345 rows=36 loops=999)

45. 7,787.252 139,989.870 ↓ 36.0 36 999

Nested Loop (cost=1.58..2,742.17 rows=1 width=4) (actual time=0.598..140.130 rows=36 loops=999)

46. 13,554.303 54,119.826 ↓ 336.9 19,540 999

Nested Loop (cost=1.15..2,715.34 rows=58 width=4) (actual time=0.076..54.174 rows=19,540 loops=999)

47. 202.797 202.797 ↓ 13.0 13 999

Index Scan using index_sp_tracks_on_sanitized_isrc_and_popularity_desc on sp_tracks s (cost=0.57..2.58 rows=1 width=4) (actual time=0.042..0.203 rows=13 loops=999)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
48. 40,362.726 40,362.726 ↑ 2.0 1,455 13,414

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings sp_playlist_listings_1 (cost=0.58..2,683.92 rows=2,883 width=8) (actual time=0.021..3.009 rows=1,455 loops=13,414)

  • Index Cond: (sp_track_id = s.id)
49. 78,082.792 78,082.792 ↓ 0.0 0 19,520,698

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions sp_playlist_editions_1 (cost=0.43..0.45 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=19,520,698)

  • Index Cond: (id = sp_playlist_listings_1.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 1
50. 144.288 144.288 ↑ 1.0 1 36,072

Index Scan using sp_playlists_pkey on sp_playlists sp_playlists_1 (cost=0.29..0.56 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=36,072)

  • Index Cond: (id = sp_playlist_editions_1.sp_playlist_id)
Planning time : 12.112 ms
Execution time : 250,981.193 ms