explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UaFE

Settings
# exclusive inclusive rows x rows loops node
1. 0.736 723.542 ↑ 4.6 10 1

Nested Loop Left Join (cost=20.40..12,931.14 rows=46 width=979) (actual time=72.525..723.542 rows=10 loops=1)

2. 0.501 6.996 ↑ 4.6 10 1

Nested Loop Left Join (cost=7.02..1,925.29 rows=46 width=1,109) (actual time=0.211..6.996 rows=10 loops=1)

3. 0.031 6.465 ↑ 4.6 10 1

Nested Loop Left Join (cost=2.40..1,686.15 rows=46 width=1,070) (actual time=0.139..6.465 rows=10 loops=1)

4. 0.034 6.364 ↑ 4.6 10 1

Nested Loop (cost=1.98..1,656.72 rows=46 width=867) (actual time=0.129..6.364 rows=10 loops=1)

5. 0.025 6.255 ↑ 3.1 15 1

Nested Loop (cost=1.56..1,598.59 rows=46 width=518) (actual time=0.117..6.255 rows=15 loops=1)

6. 0.047 6.140 ↑ 3.1 15 1

Nested Loop (cost=1.27..1,583.43 rows=46 width=502) (actual time=0.104..6.140 rows=15 loops=1)

7. 0.033 6.018 ↑ 3.1 15 1

Nested Loop (cost=0.98..1,565.08 rows=46 width=430) (actual time=0.094..6.018 rows=15 loops=1)

8. 0.016 5.865 ↑ 3.1 15 1

Nested Loop (cost=0.56..1,189.07 rows=46 width=121) (actual time=0.082..5.865 rows=15 loops=1)

9. 0.002 0.042 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.56..16.82 rows=1 width=93) (actual time=0.039..0.042 rows=1 loops=1)

10. 0.004 0.035 ↑ 1.0 1 1

Nested Loop (cost=0.42..16.54 rows=1 width=33) (actual time=0.032..0.035 rows=1 loops=1)

11. 0.018 0.018 ↑ 1.0 1 1

Index Scan using am_playlist_editions_pkey on am_playlist_editions edition (cost=0.28..8.29 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=1)

  • Index Cond: (id = 493)
12. 0.013 0.013 ↑ 1.0 1 1

Index Scan using am_playlists_pkey on am_playlists playlist (cost=0.14..8.16 rows=1 width=29) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (id = edition.am_playlist_id)
13. 0.005 0.005 ↑ 1.0 1 1

Index Scan using am_curators_pkey on am_curators curator (cost=0.14..0.28 rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (id = playlist.am_curator_id)
14. 5.807 5.807 ↑ 3.1 15 1

Seq Scan on am_processed_playlist_listings listing (cost=0.00..1,171.79 rows=46 width=36) (actual time=0.041..5.807 rows=15 loops=1)

  • Filter: (last_am_playlist_edition_id = 493)
  • Rows Removed by Filter: 21,328
15. 0.120 0.120 ↑ 1.0 1 15

Index Scan using it_songs_pkey on it_songs track (cost=0.42..8.17 rows=1 width=317) (actual time=0.008..0.008 rows=1 loops=15)

  • Index Cond: (id = listing.it_song_id)
16. 0.075 0.075 ↑ 1.0 1 15

Index Scan using it_collections_pkey on it_collections collection (cost=0.29..0.40 rows=1 width=80) (actual time=0.005..0.005 rows=1 loops=15)

  • Index Cond: (id = track.it_collection_id)
17. 0.090 0.090 ↑ 1.0 1 15

Index Scan using it_accounts_pkey on it_accounts account (cost=0.29..0.33 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=15)

  • Index Cond: (id = track.it_account_id)
18. 0.075 0.075 ↑ 1.0 1 15

Index Scan using artists_pkey on artists (cost=0.42..1.26 rows=1 width=353) (actual time=0.005..0.005 rows=1 loops=15)

  • Index Cond: (id = account.artist_id)
19. 0.070 0.070 ↑ 1.0 1 10

Index Scan using index_sp_accounts_on_artist_id on sp_accounts (cost=0.42..0.64 rows=1 width=207) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (artist_id = artists.id)
20. 0.000 0.030 ↓ 0.0 0 10

Index Scan using sp_tracks_pkey on sp_tracks sp_tracks_from_isrc (cost=4.62..5.20 rows=1 width=43) (actual time=0.003..0.003 rows=0 loops=10)

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

SubPlan (for Index Scan)

22. 0.020 0.450 ↓ 0.0 0 10

Limit (cost=0.42..4.20 rows=1 width=8) (actual time=0.045..0.045 rows=0 loops=10)

23. 0.430 0.430 ↓ 0.0 0 10

Index Scan using index_sp_tracks_on_sanitized_isrc_and_popularity_desc on sp_tracks sp_tracks_2 (cost=0.42..7,603.67 rows=2,014 width=8) (actual time=0.043..0.043 rows=0 loops=10)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (track.isrc)::citext)
24. 0.000 0.020 ↓ 0.0 0 10

Index Scan using sp_tracks_pkey on sp_tracks (cost=13.38..13.55 rows=1 width=35) (actual time=0.002..0.002 rows=0 loops=10)

  • Index Cond: (id = (SubPlan 10))
25.          

SubPlan (for Index Scan)

26. 0.030 0.380 ↓ 0.0 0 10

Limit (cost=12.95..12.95 rows=1 width=16) (actual time=0.037..0.038 rows=0 loops=10)

27. 0.140 0.350 ↓ 0.0 0 10

Sort (cost=12.95..12.95 rows=1 width=16) (actual time=0.035..0.035 rows=0 loops=10)

  • Sort Key: spt_view.popularity DESC NULLS LAST, spt_view.updated_at DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
28. 0.050 0.210 ↑ 1.0 1 10

Bitmap Heap Scan on sp_tracks spt_view (cost=8.91..12.94 rows=1 width=16) (actual time=0.020..0.021 rows=1 loops=10)

  • Recheck Cond: (((primary_sp_account_id = sp_tracks_from_isrc.primary_sp_account_id) AND ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks_from_isrc.isrc)::text, '-'::text, ''::text)))::citext)) OR ((primary_sp_account_id = sp_tracks_from_isrc.primary_sp_account_id) AND ((name)::text = (sp_tracks_from_isrc.name)::text) AND (name IS NOT NULL)))
  • Filter: ((((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks_from_isrc.isrc)::text, '-'::text, ''::text)))::citext) AND (isrc IS NOT NULL)) OR (((name)::text = (sp_tracks_from_isrc.name)::text) AND (name IS NOT NULL)))
  • Heap Blocks: exact=6
29. 0.030 0.160 ↓ 0.0 0 10

BitmapOr (cost=8.91..8.91 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=10)

30. 0.080 0.080 ↓ 0.0 0 10

Bitmap Index Scan on index_sp_tracks_on_primary_account_id_sanitized_isrc_name_popul (cost=0.00..4.44 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=10)

  • Index Cond: ((primary_sp_account_id = sp_tracks_from_isrc.primary_sp_account_id) AND ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks_from_isrc.isrc)::text, '-'::text, ''::text)))::citext))
31. 0.050 0.050 ↑ 1.0 1 10

Bitmap Index Scan on index_sp_tracks_on_primary_account_id_sanitized_isrc_name_popul (cost=0.00..4.47 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: ((primary_sp_account_id = sp_tracks_from_isrc.primary_sp_account_id) AND ((name)::text = (sp_tracks_from_isrc.name)::text) AND (name IS NOT NULL))
32.          

SubPlan (for Nested Loop Left Join)

33. 0.030 0.420 ↑ 1.0 1 10

Aggregate (cost=53.41..53.42 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=10)

34. 0.066 0.390 ↑ 4.0 1 10

Nested Loop (cost=4.74..53.40 rows=4 width=8) (actual time=0.038..0.039 rows=1 loops=10)

35. 0.040 0.280 ↑ 4.0 1 10

Bitmap Heap Scan on it_songs (cost=4.45..20.17 rows=4 width=4) (actual time=0.027..0.028 rows=1 loops=10)

  • Recheck Cond: ((isrc)::text = (track.isrc)::text)
  • Heap Blocks: exact=11
36. 0.240 0.240 ↑ 4.0 1 10

Bitmap Index Scan on index_it_songs_on_isrc (cost=0.00..4.45 rows=4 width=0) (actual time=0.024..0.024 rows=1 loops=10)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
37. 0.044 0.044 ↑ 1.0 1 11

Index Scan using it_collections_pkey on it_collections (cost=0.29..8.31 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: (id = it_songs.it_collection_id)
38. 0.040 705.260 ↑ 1.0 1 10

Limit (cost=18.95..18.95 rows=1 width=12) (actual time=70.524..70.526 rows=1 loops=10)

39. 0.080 705.220 ↑ 1.0 1 10

Sort (cost=18.95..18.95 rows=1 width=12) (actual time=70.522..70.522 rows=1 loops=10)

  • Sort Key: am_playlist_listings.created_at DESC
  • Sort Method: quicksort Memory: 25kB
40. 0.140 705.140 ↑ 1.0 1 10

Merge Join (cost=17.61..18.94 rows=1 width=12) (actual time=64.059..70.514 rows=1 loops=10)

  • Merge Cond: (it_songs_1.id = am_playlist_listings.it_song_id)
41. 703.830 703.830 ↑ 55,014.0 1 10

Index Scan using it_songs_pkey on it_songs it_songs_1 (cost=0.42..8,958.95 rows=55,014 width=4) (actual time=63.928..70.383 rows=1 loops=10)

  • Filter: CASE WHEN (track.isrc IS NOT NULL) THEN ((isrc)::text = (track.isrc)::text) ELSE (it_id = track.it_id) END
  • Rows Removed by Filter: 109,972
42. 0.220 1.170 ↓ 9.0 9 10

Sort (cost=17.18..17.19 rows=1 width=16) (actual time=0.116..0.117 rows=9 loops=10)

  • Sort Key: am_playlist_listings.it_song_id
  • Sort Method: quicksort Memory: 26kB
43. 0.300 0.950 ↓ 15.0 15 10

Nested Loop (cost=4.59..17.17 rows=1 width=16) (actual time=0.087..0.095 rows=15 loops=10)

44. 0.130 0.330 ↓ 4.0 4 10

Bitmap Heap Scan on am_playlist_editions (cost=4.29..8.08 rows=1 width=4) (actual time=0.028..0.033 rows=4 loops=10)

  • Recheck Cond: (am_playlist_id = playlist.id)
  • Filter: (id < edition.id)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=20
45. 0.200 0.200 ↓ 3.5 7 10

Bitmap Index Scan on index_am_playlist_editions_on_am_playlist_id (cost=0.00..4.29 rows=2 width=0) (actual time=0.020..0.020 rows=7 loops=10)

  • Index Cond: (am_playlist_id = playlist.id)
46. 0.320 0.320 ↓ 4.0 4 40

Index Scan using index_am_playlist_listings_on_edition_id_and_it_song_id_and_cre on am_playlist_listings (cost=0.30..9.08 rows=1 width=20) (actual time=0.007..0.008 rows=4 loops=40)

  • Index Cond: ((am_playlist_edition_id = am_playlist_editions.id) AND (created_at <= (LOCALTIMESTAMP - '18:00:00'::interval)) AND (created_at >= (LOCALTIMESTAMP - '2 days'::interval)))
47. 0.010 0.060 ↓ 0.0 0 10

Limit (cost=0.43..8.45 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=10)

48. 0.018 0.050 ↓ 0.0 0 10

Result (cost=0.43..8.45 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=10)

  • One-Time Filter: (sp_tracks.id IS NOT NULL)
49. 0.032 0.032 ↓ 0.0 0 4

Index Scan using index_sp_track_popularities_on_sp_track_id_and_created_at on sp_track_popularities (cost=0.43..8.45 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=4)

  • Index Cond: ((sp_tracks.id = sp_track_id) AND (created_at >= (LOCALTIMESTAMP - '7 days 12:00:00'::interval)) AND (created_at <= (LOCALTIMESTAMP - '6 days 12:00:00'::interval)))
50. 0.030 0.190 ↑ 1.0 1 10

Aggregate (cost=8.47..8.48 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=10)

51. 0.010 0.160 ↓ 0.0 0 10

Limit (cost=8.45..8.46 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=10)

52. 0.020 0.150 ↓ 0.0 0 10

Unique (cost=8.45..8.46 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=10)

53. 0.090 0.130 ↓ 0.0 0 10

Sort (cost=8.45..8.46 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=10)

  • Sort Key: ((sp_track_popularities_1.created_at)::date)
  • Sort Method: quicksort Memory: 25kB
54. 0.040 0.040 ↓ 0.0 0 10

Index Only Scan using index_sp_track_popularities_on_sp_track_id_and_created_at on sp_track_popularities sp_track_popularities_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=10)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Heap Fetches: 4
55. 0.020 0.170 ↓ 0.0 0 10

Limit (cost=8.48..8.48 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=10)

56. 0.040 0.150 ↓ 0.0 0 10

Sort (cost=8.48..8.48 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=10)

  • Sort Key: x.popularity
  • Sort Method: quicksort Memory: 25kB
57. 0.010 0.110 ↓ 0.0 0 10

Subquery Scan on x (cost=8.45..8.47 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=10)

58. 0.010 0.100 ↓ 0.0 0 10

Limit (cost=8.45..8.46 rows=1 width=16) (actual time=0.010..0.010 rows=0 loops=10)

59. 0.010 0.090 ↓ 0.0 0 10

Unique (cost=8.45..8.46 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=10)

60. 0.050 0.080 ↓ 0.0 0 10

Sort (cost=8.45..8.46 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=10)

  • Sort Key: ((sp_track_popularities_2.created_at)::date) DESC
  • Sort Method: quicksort Memory: 25kB
61. 0.030 0.030 ↓ 0.0 0 10

Index Scan using index_sp_track_popularities_on_sp_track_id_and_created_at on sp_track_popularities sp_track_popularities_2 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=10)

  • Index Cond: (sp_tracks.id = sp_track_id)
62. 0.020 0.520 ↑ 1.0 1 10

Aggregate (cost=24.49..24.50 rows=1 width=8) (actual time=0.052..0.052 rows=1 loops=10)

63. 0.010 0.500 ↑ 2.0 1 10

Result (cost=1.42..24.48 rows=2 width=4) (actual time=0.047..0.050 rows=1 loops=10)

  • One-Time Filter: (track.isrc IS NOT NULL)
64. 0.030 0.490 ↑ 2.0 1 10

Nested Loop (cost=1.42..24.48 rows=2 width=4) (actual time=0.045..0.049 rows=1 loops=10)

65. 0.010 0.430 ↑ 2.0 1 10

Nested Loop (cost=1.13..23.27 rows=2 width=4) (actual time=0.041..0.043 rows=1 loops=10)

66. 0.030 0.390 ↑ 2.0 1 10

Nested Loop (cost=0.85..22.67 rows=2 width=4) (actual time=0.037..0.039 rows=1 loops=10)

67. 0.320 0.320 ↓ 0.0 0 10

Index Scan using index_sp_tracks_on_isrc on sp_tracks sp_tracks_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.031..0.032 rows=0 loops=10)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
68. 0.040 0.040 ↑ 5.0 1 5

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings (cost=0.42..14.18 rows=5 width=8) (actual time=0.007..0.008 rows=1 loops=5)

  • Index Cond: (sp_track_id = sp_tracks_1.id)
69. 0.030 0.030 ↑ 1.0 1 6

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.28..0.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
70. 0.030 0.030 ↑ 1.0 1 6

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

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
71. 1.740 4.320 ↑ 1.0 1 10

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

72. 0.195 2.580 ↓ 7.8 31 10

Nested Loop (cost=0.85..50.33 rows=4 width=36) (actual time=0.021..0.258 rows=31 loops=10)

73. 0.830 0.830 ↓ 7.8 31 10

Index Scan using index_sp_account_tracks_on_sp_account_id on sp_account_tracks (cost=0.42..16.57 rows=4 width=4) (actual time=0.013..0.083 rows=31 loops=10)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 35
74. 1.555 1.555 ↑ 1.0 1 311

Index Scan using sp_tracks_pkey on sp_tracks s (cost=0.42..8.44 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=311)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
75. 4.090 4.850 ↑ 1.0 1 10

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

76. 0.760 0.760 ↓ 6.0 84 10

Index Scan using index_it_songs_on_it_account_id on it_songs it_songs_2 (cost=0.42..52.89 rows=14 width=17) (actual time=0.010..0.076 rows=84 loops=10)

  • Index Cond: (it_account_id = account.id)
Planning time : 33.356 ms
Execution time : 724.294 ms