explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bU2o

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=21,409.85..66,503.43 rows=1 width=660) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=21,409.43..21,433.14 rows=1 width=840) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=21,409.01..21,432.57 rows=1 width=645) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=21,408.58..21,432.06 rows=1 width=641) (actual rows= loops=)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=21,408.29..21,431.65 rows=1 width=491) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=21,408.01..21,431.32 rows=1 width=487) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=21.06..36.35 rows=1 width=354) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..7.70 rows=1 width=112) (actual rows= loops=)

  • Join Filter: (occ_chart_editions.occ_chart_source_id = occ_chart_sources.id)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..6.68 rows=1 width=120) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using index_occ_chart_listings_on_occ_chart_edition_id on occ_chart_listings (cost=0.42..4.48 rows=1 width=112) (actual rows= loops=)

  • Index Cond: (occ_chart_edition_id = 17)
  • Filter: (("position" >= 11,000) AND ("position" <= 11,999))
11. 0.000 0.000 ↓ 0.0

Seq Scan on occ_chart_editions (cost=0.00..2.19 rows=1 width=16) (actual rows= loops=)

  • Filter: (id = 17)
12. 0.000 0.000 ↓ 0.0

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

13. 0.000 0.000 ↓ 0.0

Index Scan using it_songs_pkey on it_songs (cost=20.63..28.65 rows=1 width=242) (actual rows= loops=)

  • Index Cond: (id = (SubPlan 15))
14.          

SubPlan (for Index Scan)

15. 0.000 0.000 ↓ 0.0

Limit (cost=20.21..20.22 rows=1 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=20.21..20.22 rows=4 width=8) (actual rows= loops=)

  • Sort Key: (char_length((it_songs_2.composer_names)::text)) DESC NULLS LAST
17. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on it_songs it_songs_2 (cost=4.46..20.19 rows=4 width=8) (actual rows= loops=)

  • Recheck Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::text))
18. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_it_songs_on_isrc (cost=0.00..4.46 rows=4 width=0) (actual rows= loops=)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::text))
19. 0.000 0.000 ↓ 0.0

Index Scan using sp_tracks_pkey on sp_tracks (cost=21,386.95..21,394.96 rows=1 width=133) (actual rows= loops=)

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

SubPlan (for Index Scan)

21. 0.000 0.000 ↓ 0.0

Limit (cost=21,386.52..21,386.52 rows=1 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=21,386.52..21,391.56 rows=2,014 width=8) (actual rows= loops=)

  • Sort Key: sp_tracks_2.popularity DESC NULLS LAST
23. 0.000 0.000 ↓ 0.0

Seq Scan on sp_tracks sp_tracks_2 (cost=0.00..21,376.45 rows=2,014 width=8) (actual rows= loops=)

  • Filter: ((isrc IS NOT NULL) AND ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::citext))
24. 0.000 0.000 ↓ 0.0

Index Scan using it_accounts_pkey on it_accounts (cost=0.29..0.33 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (it_songs.it_account_id = id)
25. 0.000 0.000 ↓ 0.0

Index Scan using it_collections_pkey on it_collections (cost=0.29..0.40 rows=1 width=162) (actual rows= loops=)

  • Index Cond: (id = it_songs.it_collection_id)
26. 0.000 0.000 ↓ 0.0

Index Scan using index_sp_account_tracks_on_sp_track_id_and_primary on sp_account_tracks (cost=0.42..0.50 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Filter: (NOT inactive)
27. 0.000 0.000 ↓ 0.0

Index Scan using sp_accounts_pkey on sp_accounts (cost=0.42..0.57 rows=1 width=199) (actual rows= loops=)

  • Index Cond: (id = sp_account_tracks.sp_account_id)
28. 0.000 0.000 ↓ 0.0

Index Scan using artists_pkey on artists (cost=0.42..1.27 rows=1 width=353) (actual rows= loops=)

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

SubPlan (for Nested Loop Left Join)

30. 0.000 0.000 ↓ 0.0

Aggregate (cost=50.34..50.35 rows=1 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..50.33 rows=4 width=36) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using index_sp_account_tracks_on_sp_account_id on sp_account_tracks sp_account_tracks_1 (cost=0.42..16.57 rows=4 width=4) (actual rows= loops=)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
33. 0.000 0.000 ↓ 0.0

Index Scan using sp_tracks_pkey on sp_tracks sp_tracks_1 (cost=0.42..8.44 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (id = sp_account_tracks_1.sp_track_id)
34. 0.000 0.000 ↓ 0.0

Aggregate (cost=53.03..53.04 rows=1 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Scan using index_it_songs_on_it_account_id on it_songs it_songs_1 (cost=0.42..52.89 rows=14 width=17) (actual rows= loops=)

  • Index Cond: (it_account_id = it_accounts.id)
36. 0.000 0.000 ↓ 0.0

Aggregate (cost=44,634.35..44,634.36 rows=1 width=8) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

HashAggregate (cost=44,549.86..44,587.41 rows=3,755 width=8) (actual rows= loops=)

  • Group Key: sp_playlists.id, sp_playlists.followers_count
38. 0.000 0.000 ↓ 0.0

Append (cost=0.99..44,531.08 rows=3,755 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..58.66 rows=5 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..55.68 rows=5 width=4) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings (cost=0.42..14.18 rows=5 width=4) (actual rows= loops=)

  • Index Cond: (sp_track_id = sp_tracks.id)
42. 0.000 0.000 ↓ 0.0

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
43. 0.000 0.000 ↓ 0.0

Index Scan using sp_playlists_pkey on sp_playlists (cost=0.29..0.60 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
44. 0.000 0.000 ↓ 0.0

Hash Join (cost=24,340.52..44,434.87 rows=3,750 width=8) (actual rows= loops=)

  • Hash Cond: (sp_playlist_editions_1.sp_playlist_id = sp_playlists_1.id)
45. 0.000 0.000 ↓ 0.0

Hash Join (cost=23,646.99..43,731.50 rows=3,750 width=4) (actual rows= loops=)

  • Hash Cond: (sp_playlist_listings_1.sp_playlist_edition_id = sp_playlist_editions_1.id)
46. 0.000 0.000 ↓ 0.0

Hash Join (cost=23,418.04..43,492.37 rows=3,876 width=4) (actual rows= loops=)

  • Hash Cond: (sp_playlist_listings_1.sp_track_id = s.id)
47. 0.000 0.000 ↓ 0.0

Seq Scan on sp_playlist_listings sp_playlist_listings_1 (cost=0.00..18,037.03 rows=776,103 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=23,392.87..23,392.87 rows=2,014 width=4) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on sp_tracks s (cost=0.00..23,392.87 rows=2,014 width=4) (actual rows= loops=)

  • Filter: ((isrc IS NOT NULL) AND ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext))
50. 0.000 0.000 ↓ 0.0

Hash (cost=143.59..143.59 rows=6,829 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on sp_playlist_editions sp_playlist_editions_1 (cost=0.00..143.59 rows=6,829 width=8) (actual rows= loops=)

  • Filter: current
52. 0.000 0.000 ↓ 0.0

Hash (cost=569.90..569.90 rows=9,890 width=8) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on sp_playlists sp_playlists_1 (cost=0.00..569.90 rows=9,890 width=8) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((occ_chart_edition_id = 16) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
56. 0.000 0.000 ↓ 0.0

Aggregate (cost=56.10..56.11 rows=1 width=4) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on occ_chart_listings ocl_1 (cost=4.56..56.07 rows=13 width=4) (actual rows= loops=)

  • Recheck Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::bigint[]))
58. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_occ_chart_listings_on_isrc_and_year_week (cost=0.00..4.55 rows=13 width=0) (actual rows= loops=)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
59. 0.000 0.000 ↓ 0.0

Aggregate (cost=56.10..56.11 rows=1 width=8) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on occ_chart_listings ocl_2 (cost=4.56..56.07 rows=13 width=0) (actual rows= loops=)

  • Recheck Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::bigint[]))
61. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_occ_chart_listings_on_isrc_and_year_week (cost=0.00..4.55 rows=13 width=0) (actual rows= loops=)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
62. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_3 (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((occ_chart_edition_id = 16) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
64. 0.000 0.000 ↓ 0.0

Aggregate (cost=56.10..56.11 rows=1 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on occ_chart_listings ocl_4 (cost=4.56..56.07 rows=13 width=4) (actual rows= loops=)

  • Recheck Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::bigint[]))
66. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_occ_chart_listings_on_isrc_and_year_week (cost=0.00..4.55 rows=13 width=0) (actual rows= loops=)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
67. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_5 (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((occ_chart_edition_id = 16) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
69. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_6 (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((occ_chart_edition_id = 16) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
71. 0.000 0.000 ↓ 0.0

Aggregate (cost=56.10..56.11 rows=1 width=8) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on occ_chart_listings ocl_7 (cost=4.56..56.07 rows=13 width=0) (actual rows= loops=)

  • Recheck Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::bigint[]))
73. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_occ_chart_listings_on_isrc_and_year_week (cost=0.00..4.55 rows=13 width=0) (actual rows= loops=)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
74. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_8 (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((occ_chart_edition_id = 16) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
76. 0.000 0.000 ↓ 0.0

Aggregate (cost=56.10..56.11 rows=1 width=8) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on occ_chart_listings ocl_9 (cost=4.56..56.07 rows=13 width=0) (actual rows= loops=)

  • Recheck Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}'::bigint[]))
78. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_occ_chart_listings_on_isrc_and_year_week (cost=0.00..4.55 rows=13 width=0) (actual rows= loops=)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
79. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_10 (cost=0.42..8.45 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((occ_chart_edition_id = 16) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))