explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jCRt

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 488.306 ↑ 1.0 1 1

Sort (cost=44,234.02..44,234.02 rows=1 width=129) (actual time=488.305..488.306 rows=1 loops=1)

  • Sort Key: window_results.current_playlists_follower_count_sum
  • Sort Method: quicksort Memory: 25kB
2. 0.037 488.297 ↑ 1.0 1 1

Subquery Scan on window_results (cost=21,398.92..44,234.01 rows=1 width=129) (actual time=365.620..488.297 rows=1 loops=1)

3. 9.752 473.569 ↑ 1.0 1 1

Unique (cost=21,398.92..21,412.57 rows=1 width=182) (actual time=350.893..473.569 rows=1 loops=1)

4. 98.521 463.817 ↓ 84.9 35,643 1

WindowAgg (cost=21,398.92..21,412.57 rows=420 width=182) (actual time=350.890..463.817 rows=35,643 loops=1)

5. 216.352 365.296 ↓ 84.9 35,643 1

Sort (cost=21,398.92..21,399.97 rows=420 width=138) (actual time=350.871..365.296 rows=35,643 loops=1)

  • Sort Key: (COALESCE(sp_tracks.isrc, (sp_tracks.id)::character varying(255))), nielsen_song_metrics_video.year DESC, nielsen_song_metrics_video.week DESC
  • Sort Method: external sort Disk: 4944kB
6. 71.540 148.944 ↓ 84.9 35,643 1

WindowAgg (cost=21,361.72..21,380.62 rows=420 width=138) (actual time=97.896..148.944 rows=35,643 loops=1)

7. 28.049 77.404 ↓ 84.9 35,643 1

Sort (cost=21,361.72..21,362.77 rows=420 width=102) (actual time=68.265..77.404 rows=35,643 loops=1)

  • Sort Key: (COALESCE(sp_tracks.isrc, (sp_tracks.id)::character varying(255))), sp_playlist_listings.official_created_at DESC, sp_playlist_listings.created_at DESC
  • Sort Method: quicksort Memory: 6239kB
8. 20.507 49.355 ↓ 84.9 35,643 1

Nested Loop Left Join (cost=4.72..21,343.42 rows=420 width=102) (actual time=0.125..49.355 rows=35,643 loops=1)

9. 0.220 8.901 ↓ 9.3 327 1

Nested Loop Left Join (cost=4.15..20,966.47 rows=35 width=79) (actual time=0.116..8.901 rows=327 loops=1)

10. 0.007 8.450 ↑ 3.3 3 1

Nested Loop (cost=3.58..20,895.73 rows=10 width=71) (actual time=0.101..8.450 rows=3 loops=1)

11. 0.034 8.350 ↓ 3.0 3 1

Nested Loop Left Join (cost=2.17..20,735.95 rows=1 width=75) (actual time=0.080..8.350 rows=3 loops=1)

  • Join Filter: (sp_tracks.isrc IS NOT NULL)
12. 0.008 8.211 ↓ 3.0 3 1

Nested Loop (cost=1.73..20,727.49 rows=1 width=67) (actual time=0.037..8.211 rows=3 loops=1)

13. 0.007 8.179 ↓ 3.0 3 1

Nested Loop (cost=1.29..20,719.02 rows=1 width=25) (actual time=0.029..8.179 rows=3 loops=1)

14. 0.489 8.154 ↓ 3.0 3 1

Nested Loop (cost=1.01..20,711.44 rows=1 width=25) (actual time=0.024..8.154 rows=3 loops=1)

15. 1.609 1.609 ↑ 1.1 1,514 1

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings (cost=0.57..6,520.52 rows=1,702 width=24) (actual time=0.015..1.609 rows=1,514 loops=1)

  • Index Cond: (sp_track_id = 807038)
16. 6.056 6.056 ↓ 0.0 0 1,514

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.43..8.33 rows=1 width=9) (actual time=0.004..0.004 rows=0 loops=1,514)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: (current AND current)
  • Rows Removed by Filter: 1
17. 0.018 0.018 ↑ 1.0 1 3

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

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
18. 0.024 0.024 ↑ 1.0 1 3

Index Scan using sp_tracks_pkey on sp_tracks (cost=0.43..8.45 rows=1 width=42) (actual time=0.007..0.008 rows=1 loops=3)

  • Index Cond: (id = 807038)
19. 0.105 0.105 ↑ 1.0 1 3

Index Scan using index_nielsen_songs_on_isrc on nielsen_songs (cost=0.44..8.46 rows=1 width=21) (actual time=0.034..0.035 rows=1 loops=3)

  • Index Cond: (isrc = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
20. 0.012 0.093 ↑ 10.0 1 3

Nested Loop (cost=1.41..159.68 rows=10 width=4) (actual time=0.026..0.031 rows=1 loops=3)

21. 0.009 0.054 ↑ 12.0 1 3

Nested Loop (cost=0.99..153.12 rows=12 width=8) (actual time=0.015..0.018 rows=1 loops=3)

22. 0.024 0.024 ↑ 12.0 1 3

Index Scan using index_sp_account_tracks_on_sp_track_id on sp_account_tracks (cost=0.56..51.66 rows=12 width=8) (actual time=0.007..0.008 rows=1 loops=3)

  • Index Cond: (sp_track_id = 807038)
23. 0.021 0.021 ↑ 1.0 1 3

Index Scan using sp_accounts_pkey on sp_accounts (cost=0.43..8.45 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=3)

  • Index Cond: (id = sp_account_tracks.sp_account_id)
24. 0.027 0.027 ↑ 1.0 1 3

Index Scan using artists_pkey on artists (cost=0.42..0.54 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=3)

  • Index Cond: (id = sp_accounts.artist_id)
  • Filter: ((cached_label)::text <> ALL ('{universal,sony,warner,"major independent"}'::text[]))
25. 0.231 0.231 ↓ 2.2 109 3

Index Scan using ix_nielsen_song_metrics_on_nielsen_song_id_metric_type on nielsen_song_metrics nielsen_song_metrics_video (cost=0.57..6.58 rows=49 width=16) (actual time=0.010..0.077 rows=109 loops=3)

  • Index Cond: ((nielsen_song_id = nielsen_songs.id) AND (metric_type = 5))
26. 19.947 19.947 ↑ 1.6 109 327

Index Scan using ix_nielsen_song_metrics_on_nielsen_song_id_metric_type on nielsen_song_metrics nielsen_song_metrics_total (cost=0.57..8.99 rows=169 width=12) (actual time=0.006..0.061 rows=109 loops=327)

  • Index Cond: ((nielsen_song_id = nielsen_songs.id) AND (metric_type = 3))
27.          

SubPlan (forSubquery Scan)

28. 0.005 1.024 ↑ 1.0 1 1

Limit (cost=465.46..465.47 rows=1 width=4) (actual time=1.023..1.024 rows=1 loops=1)

29. 0.267 1.019 ↑ 1.0 1 1

Aggregate (cost=465.46..465.47 rows=1 width=4) (actual time=1.019..1.019 rows=1 loops=1)

30. 0.752 0.752 ↓ 3.9 523 1

Index Scan using index_sp_track_popularities_on_sp_track_id on sp_track_popularities (cost=0.57..465.12 rows=135 width=4) (actual time=0.021..0.752 rows=523 loops=1)

  • Index Cond: (window_results.sp_track_id = sp_track_id)
31. 0.002 0.795 ↑ 1.0 1 1

Limit (cost=467.84..467.84 rows=1 width=12) (actual time=0.794..0.795 rows=1 loops=1)

32. 0.012 0.793 ↑ 1.0 1 1

Sort (cost=467.84..467.84 rows=1 width=12) (actual time=0.793..0.793 rows=1 loops=1)

  • Sort Key: sp_track_popularities_1.created_at DESC
  • Sort Method: quicksort Memory: 25kB
33. 0.781 0.781 ↑ 1.0 1 1

Index Scan using index_sp_track_popularities_on_sp_track_id on sp_track_popularities sp_track_popularities_1 (cost=0.57..467.83 rows=1 width=12) (actual time=0.046..0.781 rows=1 loops=1)

  • Index Cond: (window_results.sp_track_id = sp_track_id)
  • Filter: ((created_at >= (('now'::cstring)::timestamp without time zone - '8 days'::interval)) AND (created_at <= (('now'::cstring)::timestamp without time zone - '7 days'::interval)))
  • Rows Removed by Filter: 522
34. 0.000 0.771 ↑ 1.0 1 1

Limit (cost=467.84..467.84 rows=1 width=12) (actual time=0.771..0.771 rows=1 loops=1)

35. 0.010 0.771 ↑ 1.0 1 1

Sort (cost=467.84..467.84 rows=1 width=12) (actual time=0.771..0.771 rows=1 loops=1)

  • Sort Key: sp_track_popularities_2.created_at DESC
  • Sort Method: quicksort Memory: 25kB
36. 0.761 0.761 ↓ 2.0 2 1

Index Scan using index_sp_track_popularities_on_sp_track_id on sp_track_popularities sp_track_popularities_2 (cost=0.57..467.83 rows=1 width=12) (actual time=0.050..0.761 rows=2 loops=1)

  • Index Cond: (window_results.sp_track_id = sp_track_id)
  • Filter: ((created_at >= (('now'::cstring)::timestamp without time zone - '16 days'::interval)) AND (created_at <= (('now'::cstring)::timestamp without time zone - '14 days'::interval)))
  • Rows Removed by Filter: 521
37. 0.003 0.750 ↑ 1.0 1 1

Limit (cost=467.84..467.84 rows=1 width=12) (actual time=0.749..0.750 rows=1 loops=1)

38. 0.009 0.747 ↑ 1.0 1 1

Sort (cost=467.84..467.84 rows=1 width=12) (actual time=0.747..0.747 rows=1 loops=1)

  • Sort Key: sp_track_popularities_3.created_at DESC
  • Sort Method: quicksort Memory: 25kB
39. 0.738 0.738 ↓ 2.0 2 1

Index Scan using index_sp_track_popularities_on_sp_track_id on sp_track_popularities sp_track_popularities_3 (cost=0.57..467.83 rows=1 width=12) (actual time=0.487..0.738 rows=2 loops=1)

  • Index Cond: (window_results.sp_track_id = sp_track_id)
  • Filter: ((created_at >= (('now'::cstring)::timestamp without time zone - '30 days'::interval)) AND (created_at <= (('now'::cstring)::timestamp without time zone - '28 days'::interval)))
  • Rows Removed by Filter: 521
40. 0.002 11.351 ↑ 1.3 3 1

Limit (cost=20,952.41..20,952.42 rows=4 width=27) (actual time=11.348..11.351 rows=3 loops=1)

41. 0.014 11.349 ↑ 1.3 3 1

Sort (cost=20,952.41..20,952.42 rows=4 width=27) (actual time=11.346..11.349 rows=3 loops=1)

  • Sort Key: sp_playlists_1.followers_count DESC
  • Sort Method: quicksort Memory: 25kB
42. 0.006 11.335 ↑ 1.3 3 1

Nested Loop (cost=1.29..20,952.37 rows=4 width=27) (actual time=0.052..11.335 rows=3 loops=1)

43. 1.523 11.305 ↑ 1.3 3 1

Nested Loop (cost=1.01..20,948.91 rows=4 width=4) (actual time=0.036..11.305 rows=3 loops=1)

44. 2.212 2.212 ↑ 1.1 1,514 1

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings sp_playlist_listings_1 (cost=0.57..6,596.79 rows=1,722 width=4) (actual time=0.022..2.212 rows=1,514 loops=1)

  • Index Cond: (sp_track_id = window_results.sp_track_id)
45. 7.570 7.570 ↓ 0.0 0 1,514

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions sp_playlist_editions_1 (cost=0.43..8.32 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1,514)

  • Index Cond: (id = sp_playlist_listings_1.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 1
46. 0.024 0.024 ↑ 1.0 1 3

Index Scan using sp_playlists_pkey on sp_playlists sp_playlists_1 (cost=0.29..0.86 rows=1 width=31) (actual time=0.007..0.008 rows=1 loops=3)

  • Index Cond: (id = sp_playlist_editions_1.sp_playlist_id)
Planning time : 2.894 ms
Execution time : 491.537 ms