explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SCh6

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

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

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

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

3. 8.087 377.430 ↑ 1.0 1 1

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

4. 79.030 369.343 ↓ 84.9 35,643 1

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

5. 148.781 290.313 ↓ 84.9 35,643 1

Sort (cost=21,398.92..21,399.97 rows=420 width=138) (actual time=278.035..290.313 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. 66.063 141.532 ↓ 84.9 35,643 1

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

7. 28.021 75.469 ↓ 84.9 35,643 1

Sort (cost=21,361.72..21,362.77 rows=420 width=102) (actual time=66.019..75.469 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. 19.858 47.448 ↓ 84.9 35,643 1

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

9. 0.201 8.297 ↓ 9.3 327 1

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

10. 0.005 7.853 ↑ 3.3 3 1

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

11. 0.034 7.755 ↓ 3.0 3 1

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

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

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

13. 0.008 7.577 ↓ 3.0 3 1

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

14. 1.508 7.551 ↓ 3.0 3 1

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

15. 1.501 1.501 ↑ 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.017..1.501 rows=1,514 loops=1)

  • Index Cond: (sp_track_id = 807038)
16. 4.542 4.542 ↓ 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.003..0.003 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.108 0.108 ↑ 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.035..0.036 rows=1 loops=3)

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

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

21. 0.009 0.057 ↑ 12.0 1 3

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

22. 0.027 0.027 ↑ 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.008..0.009 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.005..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.243 0.243 ↓ 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.081 rows=109 loops=3)

  • Index Cond: ((nielsen_song_id = nielsen_songs.id) AND (metric_type = 5))
26. 19.293 19.293 ↑ 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.005..0.059 rows=109 loops=327)

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

SubPlan (forSubquery Scan)

28. 0.003 0.737 ↑ 1.0 1 1

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

29. 0.178 0.734 ↑ 1.0 1 1

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

30. 0.556 0.556 ↓ 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.017..0.556 rows=523 loops=1)

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

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

32. 0.009 0.467 ↑ 1.0 1 1

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

  • Sort Key: sp_track_popularities_1.created_at DESC
  • Sort Method: quicksort Memory: 25kB
33. 0.458 0.458 ↑ 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.031..0.458 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.003 0.453 ↑ 1.0 1 1

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

35. 0.006 0.450 ↑ 1.0 1 1

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

  • Sort Key: sp_track_popularities_2.created_at DESC
  • Sort Method: quicksort Memory: 25kB
36. 0.444 0.444 ↓ 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.033..0.444 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.002 0.495 ↑ 1.0 1 1

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

38. 0.008 0.493 ↑ 1.0 1 1

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

  • Sort Key: sp_track_popularities_3.created_at DESC
  • Sort Method: quicksort Memory: 25kB
39. 0.485 0.485 ↓ 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.337..0.485 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.017 8.156 ↑ 1.3 3 1

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

41. 0.010 8.139 ↑ 1.3 3 1

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

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

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

43. 0.406 8.107 ↑ 1.3 3 1

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

44. 1.645 1.645 ↑ 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.016..1.645 rows=1,514 loops=1)

  • Index Cond: (sp_track_id = window_results.sp_track_id)
45. 6.056 6.056 ↓ 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.004..0.004 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.012 0.012 ↑ 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.004..0.004 rows=1 loops=3)

  • Index Cond: (id = sp_playlist_editions_1.sp_playlist_id)
Planning time : 2.755 ms
Execution time : 389.998 ms