explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f34J

Settings
# exclusive inclusive rows x rows loops node
1. 27,002.199 568,997.826 ↑ 83.6 99 1

Nested Loop Left Join (cost=76.35..825,517,619.24 rows=8,275 width=741) (actual time=1,394.803..568,997.826 rows=99 loops=1)

2. 170.712 372.072 ↑ 83.6 99 1

Nested Loop Left Join (cost=6.29..47,478.49 rows=8,275 width=843) (actual time=5.731..372.072 rows=99 loops=1)

3. 0.534 196.014 ↑ 83.6 99 1

Nested Loop Left Join (cost=3.14..20,126.59 rows=8,275 width=802) (actual time=4.654..196.014 rows=99 loops=1)

4. 0.497 130.437 ↑ 33.0 99 1

Nested Loop (cost=2.71..16,299.65 rows=3,271 width=599) (actual time=4.209..130.437 rows=99 loops=1)

5. 0.520 128.940 ↑ 32.7 100 1

Nested Loop (cost=2.29..14,427.99 rows=3,271 width=449) (actual time=4.195..128.940 rows=100 loops=1)

6. 0.487 122.520 ↑ 32.7 100 1

Nested Loop (cost=1.86..12,914.71 rows=3,271 width=432) (actual time=3.622..122.520 rows=100 loops=1)

7. 0.514 44.133 ↑ 32.7 100 1

Nested Loop (cost=1.43..11,337.25 rows=3,271 width=381) (actual time=2.532..44.133 rows=100 loops=1)

8. 0.269 17.919 ↑ 32.7 100 1

Nested Loop (cost=0.99..3,348.41 rows=3,271 width=87) (actual time=1.385..17.919 rows=100 loops=1)

9. 0.003 0.014 ↑ 1.0 1 1

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

10. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (id = 20,871)
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. 17.636 17.636 ↑ 32.7 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..3,311.09 rows=3,271 width=44) (actual time=1.372..17.636 rows=100 loops=1)

  • Index Cond: (am_playlist_id = 20,871)
13. 25.700 25.700 ↑ 1.0 1 100

Index Scan using it_songs_pkey on it_songs track (cost=0.43..2.43 rows=1 width=302) (actual time=0.249..0.257 rows=1 loops=100)

  • Index Cond: (id = listing.it_song_id)
14. 77.900 77.900 ↑ 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.777..0.779 rows=1 loops=100)

  • Index Cond: (id = track.it_collection_id)
15. 5.900 5.900 ↑ 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.057..0.059 rows=1 loops=100)

  • Index Cond: (id = track.it_account_id)
16. 1.000 1.000 ↑ 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.010 rows=1 loops=100)

  • Index Cond: (id = account.artist_id)
17. 65.043 65.043 ↑ 1.0 1 99

Index Scan using index_sp_accounts_on_artist_id on sp_accounts (cost=0.43..1.16 rows=1 width=207) (actual time=0.394..0.657 rows=1 loops=99)

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

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.053..0.054 rows=1 loops=99)

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

SubPlan (for Index Scan)

20. 0.297 169.686 ↑ 1.0 1 99

Limit (cost=0.56..2.58 rows=1 width=8) (actual time=1.714..1.714 rows=1 loops=99)

21. 169.389 169.389 ↑ 1.0 1 99

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=1.711..1.711 rows=1 loops=99)

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

Index Scan using sp_tracks_pkey on sp_tracks (cost=70.07..70.17 rows=1 width=35) (actual time=0.048..0.052 rows=1 loops=99)

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

SubPlan (for Index Scan)

24. 0.297 26,996.310 ↑ 1.0 1 99

Limit (cost=69.50..69.51 rows=1 width=16) (actual time=272.690..272.690 rows=1 loops=99)

25. 2.178 26,996.013 ↑ 1.0 1 99

Sort (cost=69.50..69.51 rows=1 width=16) (actual time=272.687..272.687 rows=1 loops=99)

  • Sort Key: spt_view.popularity DESC NULLS LAST, spt_view.updated_at DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
26. 24,972.255 26,993.835 ↓ 8.0 8 99

Bitmap Heap Scan on sp_tracks spt_view (cost=4.76..69.49 rows=1 width=16) (actual time=59.637..272.665 rows=8 loops=99)

  • 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: 483
  • Heap Blocks: exact=48,693
27. 0.891 2,021.580 ↓ 0.0 0 99

BitmapOr (cost=4.76..4.76 rows=63 width=0) (actual time=20.420..20.420 rows=0 loops=99)

28. 57.222 57.222 ↓ 21.0 21 99

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.578..0.578 rows=21 loops=99)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks_from_isrc.isrc)::text, '-'::text, ''::text)))::citext)
29. 1,963.467 1,963.467 ↓ 8.6 532 99

Bitmap Index Scan on index_sp_tracks_on_name (cost=0.00..3.18 rows=62 width=0) (actual time=19.833..19.833 rows=532 loops=99)

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

SubPlan (for Nested Loop Left Join)

31. 0.594 200.475 ↑ 1.0 1 99

Aggregate (cost=11.00..11.01 rows=1 width=8) (actual time=2.025..2.025 rows=1 loops=99)

32. 0.495 199.881 ↑ 1.0 2 99

Append (cost=0.43..11.00 rows=2 width=8) (actual time=0.163..2.019 rows=2 loops=99)

33. 16.038 16.038 ↑ 1.0 1 99

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.161..0.162 rows=1 loops=99)

  • Index Cond: ((am_playlist_id = playlist.id) AND (it_song_id = track.id))
34. 0.895 183.348 ↑ 1.0 1 99

Nested Loop (cost=0.99..8.52 rows=1 width=8) (actual time=0.523..1.852 rows=1 loops=99)

35. 135.135 135.135 ↓ 2.0 4 99

Index Scan using index_it_songs_on_isrc on it_songs (cost=0.56..3.60 rows=2 width=4) (actual time=0.224..1.365 rows=4 loops=99)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (track.isrc)::text))
36. 47.318 47.318 ↓ 0.0 0 401

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.118..0.118 rows=0 loops=401)

  • Index Cond: ((am_playlist_id = playlist.id) AND (it_song_id = it_songs.id))
37. 0.594 227.403 ↑ 1.0 1 99

Aggregate (cost=8.52..8.53 rows=1 width=8) (actual time=2.297..2.297 rows=1 loops=99)

38. 1.368 226.809 ↓ 2.0 4 99

Nested Loop (cost=0.99..8.51 rows=2 width=8) (actual time=0.335..2.291 rows=4 loops=99)

39. 1.683 1.683 ↓ 2.0 4 99

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.013..0.017 rows=4 loops=99)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
40. 223.758 223.758 ↑ 1.0 1 401

Index Scan using it_collections_pkey on it_collections (cost=0.43..2.45 rows=1 width=12) (actual time=0.547..0.558 rows=1 loops=401)

  • Index Cond: (id = it_songs_1.it_collection_id)
41. 0.396 1,912.284 ↓ 0.0 0 99

Limit (cost=96,189.31..96,189.32 rows=1 width=12) (actual time=19.315..19.316 rows=0 loops=99)

42. 0.594 1,911.888 ↓ 0.0 0 99

Sort (cost=96,189.31..96,189.32 rows=1 width=12) (actual time=19.312..19.312 rows=0 loops=99)

  • Sort Key: am_playlist_listings.created_at DESC
  • Sort Method: quicksort Memory: 25kB
43. 4.851 1,911.294 ↓ 0.0 0 99

Nested Loop (cost=1.59..96,189.30 rows=1 width=12) (actual time=5.448..19.306 rows=0 loops=99)

44. 95.535 1,797.543 ↓ 100.0 100 99

Nested Loop (cost=1.15..96,186.84 rows=1 width=16) (actual time=0.521..18.157 rows=100 loops=99)

45. 80.388 80.388 ↑ 1.0 630 99

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.063..0.812 rows=630 loops=99)

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

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.026..0.026 rows=0 loops=62,370)

  • 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. 108.900 108.900 ↓ 0.0 0 9,900

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

  • 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.099 104.049 ↑ 1.0 1 99

Limit (cost=0.59..2.61 rows=1 width=12) (actual time=1.051..1.051 rows=1 loops=99)

49. 0.627 103.950 ↑ 1.0 1 99

Result (cost=0.59..2.61 rows=1 width=12) (actual time=1.050..1.050 rows=1 loops=99)

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

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=1.111..1.111 rows=1 loops=93)

  • 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.693 351.252 ↑ 1.0 1 99

Aggregate (cost=10.09..10.10 rows=1 width=8) (actual time=3.548..3.548 rows=1 loops=99)

52. 1.089 350.559 ↑ 1.2 13 99

Limit (cost=9.72..9.91 rows=15 width=4) (actual time=3.532..3.541 rows=13 loops=99)

53. 22.473 349.470 ↑ 21.0 13 99

HashAggregate (cost=9.72..13.13 rows=273 width=4) (actual time=3.526..3.530 rows=13 loops=99)

  • Group Key: (sp_track_popularities_1.created_at)::date
54. 326.997 326.997 ↓ 1.8 496 99

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.842..3.303 rows=496 loops=99)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Heap Fetches: 126
55. 0.297 35,076.294 ↑ 1.0 1 99

Limit (cost=263.34..263.34 rows=1 width=4) (actual time=354.306..354.306 rows=1 loops=99)

56. 1.188 35,075.997 ↑ 10.0 1 99

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

  • Sort Key: x.popularity
  • Sort Method: top-N heapsort Memory: 25kB
57. 0.693 35,074.809 ↑ 1.1 9 99

Subquery Scan on x (cost=263.14..263.29 rows=10 width=4) (actual time=354.274..354.291 rows=9 loops=99)

58. 0.495 35,074.116 ↑ 1.1 9 99

Limit (cost=263.14..263.19 rows=10 width=16) (actual time=354.271..354.284 rows=9 loops=99)

59. 0.891 35,073.621 ↑ 30.3 9 99

Unique (cost=263.14..264.51 rows=273 width=16) (actual time=354.270..354.279 rows=9 loops=99)

60. 48.708 35,072.730 ↑ 22.8 12 99

Sort (cost=263.14..263.82 rows=273 width=16) (actual time=354.267..354.270 rows=12 loops=99)

  • Sort Key: ((sp_track_popularities_2.created_at)::date) DESC
  • Sort Method: quicksort Memory: 37kB
61. 35,024.022 35,024.022 ↓ 1.8 496 99

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.555..353.778 rows=496 loops=99)

  • Index Cond: (sp_tracks.id = sp_track_id)
62. 1.683 485,866.854 ↑ 1.0 1 99

Aggregate (cost=2,777.46..2,777.47 rows=1 width=8) (actual time=4,907.746..4,907.746 rows=1 loops=99)

63. 1.683 485,865.171 ↓ 25.0 25 99

Result (cost=1.86..2,777.46 rows=1 width=4) (actual time=68.671..4,907.729 rows=25 loops=99)

  • One-Time Filter: (track.isrc IS NOT NULL)
64. 5.158 485,863.488 ↓ 25.0 25 99

Nested Loop (cost=1.86..2,777.46 rows=1 width=4) (actual time=68.669..4,907.712 rows=25 loops=99)

65. 1,323.388 485,848.242 ↓ 25.0 25 99

Nested Loop (cost=1.58..2,777.07 rows=1 width=4) (actual time=68.660..4,907.558 rows=25 loops=99)

66. 770.889 465,632.046 ↓ 133.4 8,674 99

Nested Loop (cost=1.14..2,747.00 rows=65 width=4) (actual time=2.333..4,703.354 rows=8,674 loops=99)

67. 94.842 94.842 ↓ 7.0 7 99

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.391..0.958 rows=7 loops=99)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
68. 464,766.315 464,766.315 ↑ 2.4 1,218 705

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.826..659.243 rows=1,218 loops=705)

  • Index Cond: (sp_track_id = sp_tracks_1.id)
69. 18,892.808 18,892.808 ↓ 0.0 0 858,764

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.43..0.45 rows=1 width=8) (actual time=0.022..0.022 rows=0 loops=858,764)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 1
70. 10.088 10.088 ↑ 1.0 1 2,522

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

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
71. 102.762 9,768.429 ↑ 1.0 1 99

Aggregate (cost=272.50..272.51 rows=1 width=8) (actual time=98.671..98.671 rows=1 loops=99)

72. 61.745 9,665.667 ↓ 5.0 337 99

Nested Loop (cost=1.12..272.33 rows=67 width=36) (actual time=0.810..97.633 rows=337 loops=99)

73. 1,752.102 1,752.102 ↓ 5.0 337 99

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.592..17.698 rows=337 loops=99)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 79
74. 7,851.820 7,851.820 ↑ 1.0 1 33,412

Index Scan using sp_tracks_pkey on sp_tracks s (cost=0.56..2.58 rows=1 width=40) (actual time=0.231..0.235 rows=1 loops=33,412)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
75. 142.362 8,111.367 ↑ 1.0 1 99

Aggregate (cost=149.58..149.59 rows=1 width=8) (actual time=81.932..81.933 rows=1 loops=99)

76. 7,969.005 7,969.005 ↓ 2.7 403 99

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.615..80.495 rows=403 loops=99)

  • Index Cond: (it_account_id = account.id)
Planning time : 11.570 ms
Execution time : 568,998.453 ms