explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kIyA

Settings
# exclusive inclusive rows x rows loops node
1. 31,685.788 99,295.933 ↑ 83.4 99 1

Nested Loop Left Join (cost=76.35..782,778,283.24 rows=8,258 width=741) (actual time=1,565.747..99,295.933 rows=99 loops=1)

2. 136.495 331.230 ↑ 83.4 99 1

Nested Loop Left Join (cost=6.29..47,297.25 rows=8,258 width=842) (actual time=16.302..331.230 rows=99 loops=1)

3. 0.612 183.548 ↑ 83.4 99 1

Nested Loop Left Join (cost=3.14..20,001.55 rows=8,258 width=801) (actual time=13.520..183.548 rows=99 loops=1)

4. 0.575 139.376 ↑ 33.0 99 1

Nested Loop (cost=2.71..16,176.69 rows=3,271 width=598) (actual time=12.654..139.376 rows=99 loops=1)

5. 0.528 131.801 ↑ 32.7 100 1

Nested Loop (cost=2.29..14,427.98 rows=3,271 width=449) (actual time=12.624..131.801 rows=100 loops=1)

6. 0.549 117.473 ↑ 32.7 100 1

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

7. 0.605 64.724 ↑ 32.7 100 1

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

8. 0.245 23.119 ↑ 32.7 100 1

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

9. 0.014 5.114 ↑ 1.0 1 1

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

10. 5.099 5.099 ↑ 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=5.098..5.099 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.760 17.760 ↑ 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=3.686..17.760 rows=100 loops=1)

  • Index Cond: (am_playlist_id = 20,871)
13. 41.000 41.000 ↑ 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.399..0.410 rows=1 loops=100)

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

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

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

Index Scan using artists_pkey on artists (cost=0.42..0.52 rows=1 width=153) (actual time=0.068..0.070 rows=1 loops=100)

  • Index Cond: (id = account.artist_id)
17. 43.560 43.560 ↑ 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.370..0.440 rows=1 loops=99)

  • Index Cond: (artist_id = artists.id)
18. 0.000 11.187 ↑ 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.112..0.113 rows=1 loops=99)

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

SubPlan (for Index Scan)

20. 0.297 135.432 ↑ 1.0 1 99

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

21. 135.135 135.135 ↑ 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.365..1.365 rows=1 loops=99)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (track.isrc)::citext)
22. 0.000 1.485 ↑ 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.010..0.015 rows=1 loops=99)

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

SubPlan (for Index Scan)

24. 0.396 31,679.010 ↑ 1.0 1 99

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

25. 2.475 31,678.614 ↑ 1.0 1 99

Sort (cost=69.50..69.51 rows=1 width=16) (actual time=319.986..319.986 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. 29,714.256 31,676.139 ↓ 8.0 8 99

Bitmap Heap Scan on sp_tracks spt_view (cost=4.76..69.49 rows=1 width=16) (actual time=60.302..319.961 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=49,059
27. 0.990 1,961.883 ↓ 0.0 0 99

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

28. 68.409 68.409 ↓ 27.0 27 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.691..0.691 rows=27 loops=99)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks_from_isrc.isrc)::text, '-'::text, ''::text)))::citext)
29. 1,892.484 1,892.484 ↓ 8.8 547 99

Bitmap Index Scan on index_sp_tracks_on_name (cost=0.00..3.18 rows=62 width=0) (actual time=19.116..19.116 rows=547 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.693 225.225 ↑ 1.0 1 99

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

32. 0.594 224.532 ↑ 1.0 2 99

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

33. 15.840 15.840 ↑ 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.160..0.160 rows=1 loops=99)

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

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

35. 174.933 174.933 ↓ 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.411..1.767 rows=4 loops=99)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (track.isrc)::text))
36. 32.080 32.080 ↓ 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.080..0.080 rows=0 loops=401)

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

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

38. 1.605 351.153 ↓ 2.0 4 99

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

39. 1.881 1.881 ↓ 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.014..0.019 rows=4 loops=99)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
40. 347.667 347.667 ↑ 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.853..0.867 rows=1 loops=401)

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

Limit (cost=93,990.13..93,990.13 rows=1 width=12) (actual time=20.013..20.013 rows=0 loops=99)

42. 0.792 1,980.891 ↓ 0.0 0 99

Sort (cost=93,990.13..93,990.13 rows=1 width=12) (actual time=20.009..20.009 rows=0 loops=99)

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

Nested Loop (cost=1.59..93,990.12 rows=1 width=12) (actual time=5.145..20.001 rows=0 loops=99)

44. 87.021 1,850.508 ↓ 100.0 100 99

Nested Loop (cost=1.15..93,987.65 rows=1 width=16) (actual time=0.341..18.692 rows=100 loops=99)

45. 79.497 79.497 ↓ 1.0 630 99

Index Scan using index_am_playlist_editions_on_am_playlist_id on am_playlist_editions (cost=0.56..1,893.58 rows=622 width=4) (actual time=0.069..0.803 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,683.990 1,683.990 ↓ 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.027..0.027 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. 118.800 118.800 ↓ 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.012..0.012 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.198 100.386 ↑ 1.0 1 99

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

49. 0.678 100.188 ↑ 1.0 1 99

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

  • One-Time Filter: (sp_tracks.id IS NOT NULL)
50. 99.510 99.510 ↑ 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.070..1.070 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.792 337.590 ↑ 1.0 1 99

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

52. 1.287 336.798 ↑ 1.2 13 99

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

53. 25.047 335.511 ↑ 21.0 13 99

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

  • Group Key: (sp_track_popularities_1.created_at)::date
54. 310.464 310.464 ↓ 1.8 497 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.532..3.136 rows=497 loops=99)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Heap Fetches: 136
55. 0.297 32,672.871 ↑ 1.0 1 99

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

56. 1.386 32,672.574 ↑ 10.0 1 99

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

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

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

58. 0.594 32,670.495 ↑ 1.1 9 99

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

59. 0.990 32,669.901 ↑ 30.3 9 99

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

60. 54.450 32,668.911 ↑ 22.8 12 99

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

  • Sort Key: ((sp_track_popularities_2.created_at)::date) DESC
  • Sort Method: quicksort Memory: 37kB
61. 32,614.461 32,614.461 ↓ 1.8 497 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.525..329.439 rows=497 loops=99)

  • Index Cond: (sp_tracks.id = sp_track_id)
62. 2.673 10,539.540 ↑ 1.0 1 99

Aggregate (cost=6.52..6.53 rows=1 width=8) (actual time=106.460..106.460 rows=1 loops=99)

63. 2.673 10,536.867 ↓ 26.0 26 99

Result (cost=1.41..6.52 rows=1 width=4) (actual time=2.425..106.433 rows=26 loops=99)

  • One-Time Filter: (track.isrc IS NOT NULL)
64. 9.131 10,534.194 ↓ 26.0 26 99

Nested Loop (cost=1.41..6.52 rows=1 width=4) (actual time=2.422..106.406 rows=26 loops=99)

65. 4.764 10,504.791 ↓ 26.0 26 99

Nested Loop (cost=1.12..6.20 rows=1 width=8) (actual time=2.410..106.109 rows=26 loops=99)

66. 124.542 124.542 ↓ 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.423..1.258 rows=7 loops=99)

  • Index Cond: ((isrc)::text = (track.isrc)::text)
67. 10,375.485 10,375.485 ↓ 2.0 4 705

Index Scan using index_processed_sp_playlist_listings_on_sp_track_id_and_current on processed_sp_playlist_listings (cost=0.56..3.60 rows=2 width=16) (actual time=1.199..14.717 rows=4 loops=705)

  • Index Cond: ((sp_track_id = sp_tracks_1.id) AND (current = true))
  • Filter: current
68. 20.272 20.272 ↑ 1.0 1 2,534

Index Scan using sp_playlists_pkey on sp_playlists (cost=0.29..0.31 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=2,534)

  • Index Cond: (id = processed_sp_playlist_listings.sp_playlist_id)
69. 115.236 10,313.919 ↑ 1.0 1 99

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

70. 44.805 10,198.683 ↓ 5.0 338 99

Nested Loop (cost=1.12..272.33 rows=67 width=36) (actual time=0.854..103.017 rows=338 loops=99)

71. 1,196.514 1,196.514 ↓ 5.0 338 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.515..12.086 rows=338 loops=99)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 79
72. 8,957.364 8,957.364 ↑ 1.0 1 33,423

Index Scan using sp_tracks_pkey on sp_tracks s (cost=0.56..2.58 rows=1 width=40) (actual time=0.264..0.268 rows=1 loops=33,423)

  • Index Cond: (id = sp_account_tracks.sp_track_id)
73. 158.895 10,754.865 ↑ 1.0 1 99

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

74. 10,595.970 10,595.970 ↓ 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.699..107.030 rows=403 loops=99)

  • Index Cond: (it_account_id = account.id)
Planning time : 74.765 ms
Execution time : 99,296.834 ms