explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IYoY : OCC discovery listings NEW

Settings
# exclusive inclusive rows x rows loops node
1. 27.108 241,407.355 ↑ 1.5 999 1

Nested Loop Left Join (cost=10.47..19,303,804.70 rows=1,498 width=1,014) (actual time=237.406..241,407.355 rows=999 loops=1)

2. 4.534 650.218 ↑ 1.5 999 1

Nested Loop Left Join (cost=10.04..24,278.22 rows=1,498 width=1,404) (actual time=1.360..650.218 rows=999 loops=1)

3. 4.248 609.720 ↑ 1.5 999 1

Nested Loop Left Join (cost=9.61..23,522.86 rows=1,498 width=1,209) (actual time=1.341..609.720 rows=999 loops=1)

4. 6.229 582.495 ↑ 1.5 999 1

Nested Loop Left Join (cost=9.18..22,641.47 rows=1,498 width=1,205) (actual time=1.319..582.495 rows=999 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 204
5. 4.770 549.293 ↑ 1.5 999 1

Nested Loop Left Join (cost=8.75..21,917.15 rows=1,498 width=1,059) (actual time=1.294..549.293 rows=999 loops=1)

6. 81.532 523.544 ↑ 1.5 999 1

Nested Loop Left Join (cost=8.32..21,223.50 rows=1,498 width=1,055) (actual time=1.286..523.544 rows=999 loops=1)

7. 131.648 429.025 ↑ 1.5 999 1

Nested Loop Left Join (cost=5.18..13,484.84 rows=1,498 width=922) (actual time=0.920..429.025 rows=999 loops=1)

8. 6.793 276.398 ↑ 1.5 999 1

Nested Loop (cost=1.12..4,390.35 rows=1,498 width=697) (actual time=0.850..276.398 rows=999 loops=1)

  • Join Filter: (occ_chart_sources.id = occ_processed_chart_listings.occ_chart_source_id)
9. 3.105 9.865 ↑ 1.5 999 1

Nested Loop (cost=0.71..1,561.25 rows=1,498 width=112) (actual time=0.087..9.865 rows=999 loops=1)

10. 0.004 0.040 ↑ 1.0 1 1

Nested Loop (cost=0.14..3.19 rows=1 width=24) (actual time=0.036..0.040 rows=1 loops=1)

  • Join Filter: (occ_chart_editions.occ_chart_source_id = occ_chart_sources.id)
11. 0.023 0.023 ↑ 1.0 1 1

Index Scan using occ_chart_editions_pkey on occ_chart_editions (cost=0.14..2.16 rows=1 width=16) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: (id = 133)
12. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on occ_chart_sources (cost=0.00..1.01 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=1)

13. 6.720 6.720 ↑ 1.5 999 1

Index Scan using index_occ_chart_listings_on_edition_id_and_position on occ_chart_listings (cost=0.56..1,543.09 rows=1,498 width=96) (actual time=0.049..6.720 rows=999 loops=1)

  • Index Cond: ((occ_chart_edition_id = 133) AND ("position" >= 0) AND ("position" <= 999))
14. 259.740 259.740 ↑ 1.0 1 999

Index Scan using index_occ_processed_chart_listings_on_pri_isrc_and_chart_source on occ_processed_chart_listings (cost=0.42..1.88 rows=1 width=622) (actual time=0.258..0.260 rows=1 loops=999)

  • Index Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
15. 0.000 20.979 ↑ 1.0 1 999

Index Scan using it_songs_pkey on it_songs (cost=4.05..6.06 rows=1 width=225) (actual time=0.020..0.021 rows=1 loops=999)

  • Index Cond: (id = (SubPlan 4))
16.          

SubPlan (for Index Scan)

17. 3.996 122.877 ↑ 1.0 1 999

Limit (cost=3.61..3.62 rows=1 width=8) (actual time=0.122..0.123 rows=1 loops=999)

18. 13.986 118.881 ↑ 2.0 1 999

Sort (cost=3.61..3.62 rows=2 width=8) (actual time=0.119..0.119 rows=1 loops=999)

  • Sort Key: (char_length((it_songs_2.composer_names)::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
19. 104.895 104.895 ↓ 4.0 8 999

Index Scan using index_it_songs_on_isrc on it_songs it_songs_2 (cost=0.56..3.60 rows=2 width=8) (actual time=0.040..0.105 rows=8 loops=999)

  • Index Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::text))
20. 0.000 12.987 ↑ 1.0 1 999

Index Scan using sp_tracks_pkey on sp_tracks (cost=3.14..5.16 rows=1 width=133) (actual time=0.012..0.013 rows=1 loops=999)

  • Index Cond: (id = (SubPlan 5))
21.          

SubPlan (for Index Scan)

22. 2.997 72.927 ↑ 1.0 1 999

Limit (cost=0.56..2.58 rows=1 width=8) (actual time=0.073..0.073 rows=1 loops=999)

23. 69.930 69.930 ↑ 1.0 1 999

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.070..0.070 rows=1 loops=999)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::citext)
24. 20.979 20.979 ↑ 1.0 1 999

Index Scan using it_accounts_pkey on it_accounts (cost=0.43..0.45 rows=1 width=8) (actual time=0.019..0.021 rows=1 loops=999)

  • Index Cond: (it_songs.it_account_id = id)
25. 26.973 26.973 ↑ 1.0 1 999

Index Scan using it_collections_pkey on it_collections (cost=0.43..0.47 rows=1 width=158) (actual time=0.025..0.027 rows=1 loops=999)

  • Index Cond: (id = it_songs.it_collection_id)
26. 22.977 22.977 ↑ 5.0 1 999

Index Scan using index_sp_account_tracks_on_sp_track_id_and_primary on sp_account_tracks (cost=0.44..0.54 rows=5 width=8) (actual time=0.022..0.023 rows=1 loops=999)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Filter: (NOT inactive)
27. 35.964 35.964 ↑ 1.0 1 999

Index Scan using sp_accounts_pkey on sp_accounts (cost=0.43..0.49 rows=1 width=199) (actual time=0.025..0.036 rows=1 loops=999)

  • Index Cond: (id = sp_account_tracks.sp_account_id)
28. 14.985 14.985 ↑ 1.0 1 999

Index Scan using artists_pkey on artists (cost=0.43..0.55 rows=1 width=154) (actual time=0.014..0.015 rows=1 loops=999)

  • Index Cond: (id = it_accounts.artist_id)
29.          

SubPlan (for Nested Loop Left Join)

30. 1,091.907 7,050.942 ↑ 1.0 1 999

Aggregate (cost=261.56..261.57 rows=1 width=8) (actual time=7.057..7.058 rows=1 loops=999)

31. 613.035 5,959.035 ↓ 5.7 362 999

Nested Loop (cost=1.00..261.40 rows=64 width=36) (actual time=0.055..5.965 rows=362 loops=999)

32. 1,362.636 1,362.636 ↓ 5.7 362 999

Index Scan using index_sp_account_tracks_on_sp_account_id_and_sp_track_id on sp_account_tracks sp_account_tracks_1 (cost=0.44..95.64 rows=64 width=4) (actual time=0.030..1.364 rows=362 loops=999)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 94
33. 3,983.364 3,983.364 ↑ 1.0 1 362,124

Index Scan using sp_tracks_pkey on sp_tracks sp_tracks_1 (cost=0.56..2.58 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=362,124)

  • Index Cond: (id = sp_account_tracks_1.sp_track_id)
34. 1,587.411 3,909.087 ↑ 1.0 1 999

Aggregate (cost=150.49..150.50 rows=1 width=8) (actual time=3.913..3.913 rows=1 loops=999)

35. 2,321.676 2,321.676 ↓ 3.0 448 999

Index Scan using index_it_songs_on_it_account_id on it_songs it_songs_1 (cost=0.43..149.01 rows=148 width=17) (actual time=0.031..2.324 rows=448 loops=999)

  • Index Cond: (it_account_id = it_accounts.id)
36. 16.983 229,755.015 ↑ 1.0 1 999

Aggregate (cost=12,457.53..12,457.54 rows=1 width=8) (actual time=229.985..229.985 rows=1 loops=999)

37. 67.932 229,738.032 ↓ 7.2 36 999

HashAggregate (cost=12,457.42..12,457.47 rows=5 width=8) (actual time=229.954..229.968 rows=36 loops=999)

  • Group Key: sp_playlists.id, sp_playlists.followers_count
38. 38.961 229,670.100 ↓ 12.2 61 999

Append (cost=1.30..12,457.39 rows=5 width=8) (actual time=0.859..229.900 rows=61 loops=999)

39. 43.075 91,146.762 ↓ 6.2 25 999

Nested Loop (cost=1.30..9,714.60 rows=4 width=8) (actual time=0.711..91.238 rows=25 loops=999)

40. 4,607.720 90,976.932 ↓ 6.2 25 999

Nested Loop (cost=1.01..9,712.31 rows=4 width=4) (actual time=0.699..91.068 rows=25 loops=999)

41. 39,060.900 39,060.900 ↓ 4.1 11,839 999

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings (cost=0.58..2,683.92 rows=2,883 width=4) (actual time=0.065..39.100 rows=11,839 loops=999)

  • Index Cond: (sp_track_id = sp_tracks.id)
42. 47,308.312 47,308.312 ↓ 0.0 0 11,827,078

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.43..2.43 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=11,827,078)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 1
43. 126.755 126.755 ↑ 1.0 1 25,351

Index Scan using sp_playlists_pkey on sp_playlists (cost=0.29..0.56 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=25,351)

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
44. 72.539 138,484.377 ↓ 36.0 36 999

Nested Loop (cost=1.87..2,742.74 rows=1 width=8) (actual time=0.608..138.623 rows=36 loops=999)

45. 4,977.297 138,267.594 ↓ 36.0 36 999

Nested Loop (cost=1.58..2,742.17 rows=1 width=4) (actual time=0.598..138.406 rows=36 loops=999)

46. 12,776.633 55,199.745 ↓ 336.9 19,542 999

Nested Loop (cost=1.15..2,715.34 rows=58 width=4) (actual time=0.069..55.255 rows=19,542 loops=999)

47. 105.894 105.894 ↓ 13.0 13 999

Index Scan using index_sp_tracks_on_sanitized_isrc_and_popularity_desc on sp_tracks s (cost=0.57..2.58 rows=1 width=4) (actual time=0.039..0.106 rows=13 loops=999)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
48. 42,317.218 42,317.218 ↑ 2.0 1,455 13,417

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings sp_playlist_listings_1 (cost=0.58..2,683.92 rows=2,883 width=8) (actual time=0.017..3.154 rows=1,455 loops=13,417)

  • Index Cond: (sp_track_id = s.id)
49. 78,090.552 78,090.552 ↓ 0.0 0 19,522,638

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions sp_playlist_editions_1 (cost=0.43..0.45 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=19,522,638)

  • Index Cond: (id = sp_playlist_listings_1.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 1
50. 144.244 144.244 ↑ 1.0 1 36,061

Index Scan using sp_playlists_pkey on sp_playlists sp_playlists_1 (cost=0.29..0.56 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=36,061)

  • Index Cond: (id = sp_playlist_editions_1.sp_playlist_id)
Planning time : 15.464 ms
Execution time : 241,408.286 ms