explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZGus

Settings
# exclusive inclusive rows x rows loops node
1. 6,594.147 18,764.492 ↑ 54.7 98 1

Nested Loop Left Join (cost=76.35..534,815,721.49 rows=5,361 width=741) (actual time=15.509..18,764.492 rows=98 loops=1)

2. 72.999 259.817 ↑ 54.7 98 1

Nested Loop Left Join (cost=6.29..30,777.92 rows=5,361 width=843) (actual time=0.124..259.817 rows=98 loops=1)

3. 0.382 177.214 ↑ 54.7 98 1

Nested Loop Left Join (cost=3.14..13,057.86 rows=5,361 width=802) (actual time=0.065..177.214 rows=98 loops=1)

4. 0.409 104.802 ↑ 21.6 98 1

Nested Loop (cost=2.71..10,578.71 rows=2,119 width=599) (actual time=0.055..104.802 rows=98 loops=1)

5. 0.399 103.493 ↑ 21.2 100 1

Nested Loop (cost=2.29..9,366.22 rows=2,119 width=449) (actual time=0.048..103.493 rows=100 loops=1)

6. 0.412 98.694 ↑ 21.2 100 1

Nested Loop (cost=1.86..8,385.90 rows=2,119 width=432) (actual time=0.040..98.694 rows=100 loops=1)

7. 0.417 76.282 ↑ 21.2 100 1

Nested Loop (cost=1.43..7,364.00 rows=2,119 width=381) (actual time=0.031..76.282 rows=100 loops=1)

8. 0.205 73.265 ↑ 21.2 100 1

Nested Loop (cost=0.99..2,173.96 rows=2,119 width=87) (actual time=0.021..73.265 rows=100 loops=1)

9. 0.003 0.013 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.56..4.61 rows=1 width=51) (actual time=0.011..0.013 rows=1 loops=1)

10. 0.009 0.009 ↑ 1.0 1 1

Index Scan using am_playlists_pkey on am_playlists playlist (cost=0.29..2.31 rows=1 width=31) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (id = 1)
11. 0.001 0.001 ↓ 0.0 0 1

Index Scan using am_curators_pkey on am_curators curator (cost=0.28..2.29 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (id = playlist.am_curator_id)
12. 73.047 73.047 ↑ 21.2 100 1

Index Scan using index_am_processed_playlist_listings_on_am_pl_id_and_current on am_processed_playlist_listings listing (cost=0.43..2,148.16 rows=2,119 width=44) (actual time=0.009..73.047 rows=100 loops=1)

  • Index Cond: (am_playlist_id = 1)
13. 2.600 2.600 ↑ 1.0 1 100

Index Scan using it_songs_pkey on it_songs track (cost=0.43..2.44 rows=1 width=302) (actual time=0.023..0.026 rows=1 loops=100)

  • Index Cond: (id = listing.it_song_id)
14. 22.000 22.000 ↑ 1.0 1 100

Index Scan using it_collections_pkey on it_collections collection (cost=0.43..0.47 rows=1 width=59) (actual time=0.219..0.220 rows=1 loops=100)

  • Index Cond: (id = track.it_collection_id)
15. 4.400 4.400 ↑ 1.0 1 100

Index Scan using it_accounts_pkey on it_accounts account (cost=0.43..0.45 rows=1 width=21) (actual time=0.043..0.044 rows=1 loops=100)

  • Index Cond: (id = track.it_account_id)
16. 0.900 0.900 ↑ 1.0 1 100

Index Scan using artists_pkey on artists (cost=0.42..0.56 rows=1 width=154) (actual time=0.008..0.009 rows=1 loops=100)

  • Index Cond: (id = account.artist_id)
17. 72.030 72.030 ↑ 1.0 1 98

Index Scan using index_sp_accounts_on_artist_id on sp_accounts (cost=0.43..1.16 rows=1 width=207) (actual time=0.168..0.735 rows=1 loops=98)

  • Index Cond: (artist_id = artists.id)
18. 0.000 9.604 ↑ 1.0 1 98

Index Scan using sp_tracks_pkey on sp_tracks sp_tracks_from_isrc (cost=3.14..3.30 rows=1 width=45) (actual time=0.097..0.098 rows=1 loops=98)

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

SubPlan (for Index Scan)

20. 0.196 72.226 ↑ 1.0 1 98

Limit (cost=0.56..2.58 rows=1 width=8) (actual time=0.737..0.737 rows=1 loops=98)

21. 72.030 72.030 ↑ 1.0 1 98

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.735..0.735 rows=1 loops=98)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (track.isrc)::citext)
22. 0.000 1.078 ↑ 1.0 1 98

Index Scan using sp_tracks_pkey on sp_tracks (cost=70.07..70.17 rows=1 width=35) (actual time=0.006..0.011 rows=1 loops=98)

  • Index Cond: (id = (SubPlan 11))
23.          

SubPlan (for Index Scan)

24. 0.294 6,589.912 ↑ 1.0 1 98

Limit (cost=69.50..69.51 rows=1 width=16) (actual time=67.243..67.244 rows=1 loops=98)

25. 1.176 6,589.618 ↑ 1.0 1 98

Sort (cost=69.50..69.51 rows=1 width=16) (actual time=67.241..67.241 rows=1 loops=98)

  • Sort Key: spt_view.popularity DESC NULLS LAST, spt_view.updated_at DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
26. 5,979.960 6,588.442 ↑ 1.0 1 98

Bitmap Heap Scan on sp_tracks spt_view (cost=4.76..69.49 rows=1 width=16) (actual time=37.388..67.229 rows=1 loops=98)

  • Recheck Cond: ((((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)))
  • Filter: (primary_sp_account_id = sp_tracks_from_isrc.primary_sp_account_id)
  • Rows Removed by Filter: 153
  • Heap Blocks: exact=15,852
27. 0.588 608.482 ↓ 0.0 0 98

BitmapOr (cost=4.76..4.76 rows=63 width=0) (actual time=6.209..6.209 rows=0 loops=98)

28. 43.708 43.708 ↓ 28.0 28 98

Bitmap Index Scan on index_sp_tracks_on_sanitized_isrc_and_popularity_desc (cost=0.00..1.57 rows=1 width=0) (actual time=0.446..0.446 rows=28 loops=98)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks_from_isrc.isrc)::text, '-'::text, ''::text)))::citext)
29. 564.186 564.186 ↓ 3.1 193 98

Bitmap Index Scan on index_sp_tracks_on_name (cost=0.00..3.18 rows=62 width=0) (actual time=5.757..5.757 rows=193 loops=98)

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

SubPlan (for Nested Loop Left Join)

31. 0.490 13.426 ↑ 1.0 1 98

Aggregate (cost=11.00..11.01 rows=1 width=8) (actual time=0.136..0.137 rows=1 loops=98)

32. 0.490 12.936 ↑ 1.0 2 98

Append (cost=0.43..11.00 rows=2 width=8) (actual time=0.011..0.132 rows=2 loops=98)

33. 0.980 0.980 ↑ 1.0 1 98

Index Scan using index_am_processed_playlist_listings_on_am_pl_id_and_it_song_id on am_processed_playlist_listings (cost=0.43..2.45 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=98)

  • Index Cond: ((am_playlist_id = playlist.id) AND (it_song_id = track.id))
34. 0.426 11.466 ↑ 1.0 1 98

Nested Loop (cost=0.99..8.52 rows=1 width=8) (actual time=0.116..0.117 rows=1 loops=98)

35. 10.584 10.584 ↑ 2.0 1 98

Index Scan using index_it_songs_on_isrc on it_songs (cost=0.56..3.60 rows=2 width=4) (actual time=0.101..0.108 rows=1 loops=98)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (track.isrc)::text))
36. 0.456 0.456 ↑ 1.0 1 114

Index Scan using index_am_processed_playlist_listings_on_am_pl_id_and_it_song_id on am_processed_playlist_listings am_processed_playlist_listings_1 (cost=0.43..2.45 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=114)

  • Index Cond: ((am_playlist_id = playlist.id) AND (it_song_id = it_songs.id))
37. 0.392 10.584 ↑ 1.0 1 98

Aggregate (cost=8.52..8.53 rows=1 width=8) (actual time=0.107..0.108 rows=1 loops=98)

38. 0.450 10.192 ↑ 2.0 1 98

Nested Loop (cost=0.99..8.51 rows=2 width=8) (actual time=0.060..0.104 rows=1 loops=98)

39. 1.078 1.078 ↑ 2.0 1 98

Index Scan using index_it_songs_on_isrc on it_songs it_songs_1 (cost=0.56..3.59 rows=2 width=4) (actual time=0.011..0.011 rows=1 loops=98)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
40. 8.664 8.664 ↑ 1.0 1 114

Index Scan using it_collections_pkey on it_collections (cost=0.43..2.45 rows=1 width=12) (actual time=0.039..0.076 rows=1 loops=114)

  • Index Cond: (id = it_songs_1.it_collection_id)
41. 0.294 651.406 ↑ 1.0 1 98

Limit (cost=96,189.31..96,189.32 rows=1 width=12) (actual time=6.647..6.647 rows=1 loops=98)

42. 0.490 651.112 ↑ 1.0 1 98

Sort (cost=96,189.31..96,189.32 rows=1 width=12) (actual time=6.644..6.644 rows=1 loops=98)

  • Sort Key: am_playlist_listings.created_at DESC
  • Sort Method: quicksort Memory: 25kB
43. 1.372 650.622 ↑ 1.0 1 98

Nested Loop (cost=1.59..96,189.30 rows=1 width=12) (actual time=0.636..6.639 rows=1 loops=98)

44. 104.370 610.050 ↓ 100.0 100 98

Nested Loop (cost=1.15..96,186.84 rows=1 width=16) (actual time=0.040..6.225 rows=100 loops=98)

45. 46.746 46.746 ↓ 1.1 669 98

Index Scan using index_am_playlist_editions_on_am_playlist_id on am_playlist_editions (cost=0.56..1,872.99 rows=637 width=4) (actual time=0.018..0.477 rows=669 loops=98)

  • Index Cond: (am_playlist_id = playlist.id)
  • Filter: (id < listing.last_am_playlist_edition_id)
  • Rows Removed by Filter: 1
46. 458.934 458.934 ↓ 0.0 0 65,562

Index Scan using index_am_playlist_listings_on_edition_id_and_it_song_id_and_cre on am_playlist_listings (cost=0.59..148.05 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=65,562)

  • Index Cond: ((am_playlist_edition_id = am_playlist_editions.id) AND (created_at <= (('now'::cstring)::timestamp without time zone - '18:00:00'::interval)) AND (created_at >= (('now'::cstring)::timestamp without time zone - '2 days'::interval)))
47. 39.200 39.200 ↓ 0.0 0 9,800

Index Scan using it_songs_pkey on it_songs it_songs_2 (cost=0.43..2.46 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=9,800)

  • Index Cond: (id = am_playlist_listings.it_song_id)
  • 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: 1
48. 0.098 38.612 ↑ 1.0 1 98

Limit (cost=0.59..2.61 rows=1 width=12) (actual time=0.394..0.394 rows=1 loops=98)

49. 0.594 38.514 ↑ 1.0 1 98

Result (cost=0.59..2.61 rows=1 width=12) (actual time=0.393..0.393 rows=1 loops=98)

  • One-Time Filter: (sp_tracks.id IS NOT NULL)
50. 37.920 37.920 ↑ 1.0 1 96

Index Scan using index_sp_track_popularities_on_sp_track_id_and_created_at on sp_track_popularities (cost=0.59..2.61 rows=1 width=12) (actual time=0.395..0.395 rows=1 loops=96)

  • Index Cond: ((sp_tracks.id = sp_track_id) AND (created_at >= (('now'::cstring)::timestamp without time zone - '7 days 12:00:00'::interval)) AND (created_at <= (('now'::cstring)::timestamp without time zone - '6 days 12:00:00'::interval)))
51. 0.490 53.704 ↑ 1.0 1 98

Aggregate (cost=10.09..10.10 rows=1 width=8) (actual time=0.548..0.548 rows=1 loops=98)

52. 0.686 53.214 ↑ 1.9 8 98

Limit (cost=9.72..9.91 rows=15 width=4) (actual time=0.538..0.543 rows=8 loops=98)

53. 1.274 52.528 ↑ 34.1 8 98

HashAggregate (cost=9.72..13.13 rows=273 width=4) (actual time=0.533..0.536 rows=8 loops=98)

  • Group Key: (sp_track_popularities_1.created_at)::date
54. 51.254 51.254 ↑ 21.0 13 98

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.58..9.04 rows=273 width=4) (actual time=0.027..0.523 rows=13 loops=98)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Heap Fetches: 220
55. 0.196 237.258 ↑ 1.0 1 98

Limit (cost=263.34..263.34 rows=1 width=4) (actual time=2.421..2.421 rows=1 loops=98)

56. 0.882 237.062 ↑ 10.0 1 98

Sort (cost=263.34..263.37 rows=10 width=4) (actual time=2.419..2.419 rows=1 loops=98)

  • Sort Key: x.popularity
  • Sort Method: top-N heapsort Memory: 25kB
57. 0.490 236.180 ↑ 1.4 7 98

Subquery Scan on x (cost=263.14..263.29 rows=10 width=4) (actual time=2.394..2.410 rows=7 loops=98)

58. 0.392 235.690 ↑ 1.4 7 98

Limit (cost=263.14..263.19 rows=10 width=16) (actual time=2.392..2.405 rows=7 loops=98)

59. 0.784 235.298 ↑ 39.0 7 98

Unique (cost=263.14..264.51 rows=273 width=16) (actual time=2.391..2.401 rows=7 loops=98)

60. 1.568 234.514 ↑ 21.0 13 98

Sort (cost=263.14..263.82 rows=273 width=16) (actual time=2.389..2.393 rows=13 loops=98)

  • Sort Key: ((sp_track_popularities_2.created_at)::date) DESC
  • Sort Method: quicksort Memory: 26kB
61. 232.946 232.946 ↑ 21.0 13 98

Index Scan using index_sp_track_popularities_on_sp_track_id_and_created_at on sp_track_popularities sp_track_popularities_2 (cost=0.58..252.10 rows=273 width=16) (actual time=0.064..2.377 rows=13 loops=98)

  • Index Cond: (sp_tracks.id = sp_track_id)
62. 1.764 2,572.990 ↑ 1.0 1 98

Aggregate (cost=2,777.46..2,777.47 rows=1 width=8) (actual time=26.254..26.255 rows=1 loops=98)

63. 2.450 2,571.226 ↓ 45.0 45 98

Result (cost=1.86..2,777.46 rows=1 width=4) (actual time=1.603..26.237 rows=45 loops=98)

  • One-Time Filter: (track.isrc IS NOT NULL)
64. 6.667 2,568.776 ↓ 45.0 45 98

Nested Loop (cost=1.86..2,777.46 rows=1 width=4) (actual time=1.601..26.212 rows=45 loops=98)

65. 22.771 2,548.882 ↓ 45.0 45 98

Nested Loop (cost=1.58..2,777.07 rows=1 width=4) (actual time=1.593..26.009 rows=45 loops=98)

66. 28.876 2,208.038 ↓ 7.1 464 98

Nested Loop (cost=1.14..2,747.00 rows=65 width=4) (actual time=0.958..22.531 rows=464 loops=98)

67. 47.530 47.530 ↑ 1.0 1 98

Index Scan using index_sp_tracks_on_isrc on sp_tracks sp_tracks_1 (cost=0.56..2.58 rows=1 width=4) (actual time=0.359..0.485 rows=1 loops=98)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
68. 2,131.632 2,131.632 ↑ 7.3 402 113

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings (cost=0.58..2,715.26 rows=2,916 width=8) (actual time=0.525..18.864 rows=402 loops=113)

  • Index Cond: (sp_track_id = sp_tracks_1.id)
69. 318.073 318.073 ↓ 0.0 0 45,439

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.43..0.45 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=45,439)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 1
70. 13.227 13.227 ↑ 1.0 1 4,409

Index Scan using sp_playlists_pkey on sp_playlists (cost=0.29..0.38 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=4,409)

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
71. 66.052 4,135.796 ↑ 1.0 1 98

Aggregate (cost=272.50..272.51 rows=1 width=8) (actual time=42.202..42.202 rows=1 loops=98)

72. 30.124 4,069.744 ↓ 3.4 225 98

Nested Loop (cost=1.12..272.33 rows=67 width=36) (actual time=1.018..41.528 rows=225 loops=98)

73. 903.266 903.266 ↓ 3.4 225 98

Index Scan using index_sp_account_tracks_on_sp_account_id_and_sp_track_id on sp_account_tracks (cost=0.56..98.80 rows=67 width=4) (actual time=0.544..9.217 rows=225 loops=98)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 64
74. 3,136.354 3,136.354 ↑ 1.0 1 22,087

Index Scan using sp_tracks_pkey on sp_tracks s (cost=0.56..2.58 rows=1 width=40) (actual time=0.138..0.142 rows=1 loops=22,087)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
75. 87.318 4,195.674 ↑ 1.0 1 98

Aggregate (cost=149.58..149.59 rows=1 width=8) (actual time=42.813..42.813 rows=1 loops=98)

76. 4,108.356 4,108.356 ↓ 1.7 251 98

Index Scan using index_it_songs_on_it_account_id on it_songs it_songs_3 (cost=0.43..148.09 rows=149 width=17) (actual time=0.522..41.922 rows=251 loops=98)

  • Index Cond: (it_account_id = account.id)
Planning time : 11.715 ms
Execution time : 18,765.332 ms