explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gWcr : OCC discovery listings OLD

Settings
# exclusive inclusive rows x rows loops node
1. 64.941 422,661.837 ↑ 1.5 999 1

Nested Loop Left Join (cost=10.05..20,719,142.18 rows=1,498 width=453) (actual time=455.482..422,661.837 rows=999 loops=1)

2. 6.322 4,682.509 ↑ 1.5 999 1

Nested Loop Left Join (cost=9.62..21,449.12 rows=1,498 width=819) (actual time=5.600..4,682.509 rows=999 loops=1)

3. 4.322 4,578.285 ↑ 1.5 999 1

Nested Loop Left Join (cost=9.19..20,693.76 rows=1,498 width=624) (actual time=5.572..4,578.285 rows=999 loops=1)

4. 6.402 4,037.500 ↑ 1.5 999 1

Nested Loop Left Join (cost=8.76..19,812.37 rows=1,498 width=620) (actual time=5.170..4,037.500 rows=999 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 204
5. 5.435 3,695.434 ↑ 1.5 999 1

Nested Loop Left Join (cost=8.33..19,088.05 rows=1,498 width=474) (actual time=5.141..3,695.434 rows=999 loops=1)

6. 800.765 3,668.021 ↑ 1.5 999 1

Nested Loop Left Join (cost=7.90..18,394.40 rows=1,498 width=470) (actual time=5.134..3,668.021 rows=999 loops=1)

7. 2,259.612 2,810.313 ↑ 1.5 999 1

Nested Loop Left Join (cost=4.76..10,655.74 rows=1,498 width=337) (actual time=5.042..2,810.313 rows=999 loops=1)

8. 2.265 123.129 ↑ 1.5 999 1

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

9. 0.015 1.640 ↑ 1.0 1 1

Nested Loop (cost=0.14..3.19 rows=1 width=8) (actual time=1.636..1.640 rows=1 loops=1)

  • Join Filter: (occ_chart_editions.occ_chart_source_id = occ_chart_sources.id)
10. 1.238 1.238 ↑ 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=1.237..1.238 rows=1 loops=1)

  • Index Cond: (id = 133)
11. 0.387 0.387 ↑ 1.0 1 1

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

12. 119.224 119.224 ↑ 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=112) (actual time=3.326..119.224 rows=999 loops=1)

  • Index Cond: ((occ_chart_edition_id = 133) AND ("position" >= 0) AND ("position" <= 999))
13. 0.000 427.572 ↑ 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.415..0.428 rows=1 loops=999)

  • Index Cond: (id = (SubPlan 15))
14.          

SubPlan (for Index Scan)

15. 3.996 2,249.748 ↑ 1.0 1 999

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

16. 15.984 2,245.752 ↑ 2.0 1 999

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

  • Sort Key: (char_length((it_songs_2.composer_names)::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
17. 2,229.768 2,229.768 ↓ 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.521..2.232 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))
18. 0.000 56.943 ↑ 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.054..0.057 rows=1 loops=999)

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

SubPlan (for Index Scan)

20. 2.997 791.208 ↑ 1.0 1 999

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

21. 788.211 788.211 ↑ 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.789..0.789 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)
22. 21.978 21.978 ↑ 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.021..0.022 rows=1 loops=999)

  • Index Cond: (it_songs.it_account_id = id)
23. 335.664 335.664 ↑ 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.287..0.336 rows=1 loops=999)

  • Index Cond: (id = it_songs.it_collection_id)
24. 536.463 536.463 ↑ 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.536..0.537 rows=1 loops=999)

  • Index Cond: (sp_track_id = sp_tracks.id)
  • Filter: (NOT inactive)
25. 97.902 97.902 ↑ 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.059..0.098 rows=1 loops=999)

  • Index Cond: (id = sp_account_tracks.sp_account_id)
26. 24.975 24.975 ↑ 1.0 1 999

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

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

SubPlan (for Nested Loop Left Join)

28. 1,171.827 15,607.377 ↑ 1.0 1 999

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

29. 399.405 14,435.550 ↓ 5.7 362 999

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

30. 2,086.911 2,086.911 ↓ 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.261..2.089 rows=362 loops=999)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 94
31. 11,949.234 11,949.234 ↑ 1.0 1 362,098

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

  • Index Cond: (id = sp_account_tracks_1.sp_track_id)
32. 1,792.206 55,425.519 ↑ 1.0 1 999

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

33. 53,633.313 53,633.313 ↓ 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.433..53.687 rows=448 loops=999)

  • Index Cond: (it_account_id = it_accounts.id)
34. 19.980 338,326.335 ↑ 1.0 1 999

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

35. 72.927 338,306.355 ↓ 7.2 36 999

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

  • Group Key: sp_playlists.id, sp_playlists.followers_count
36. 40.959 338,233.428 ↓ 12.2 61 999

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

37. 39.079 151,442.406 ↓ 6.2 25 999

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

38. 7,730.594 151,276.572 ↓ 6.2 25 999

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

39. 96,237.666 96,237.666 ↓ 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.402..96.334 rows=11,839 loops=999)

  • Index Cond: (sp_track_id = sp_tracks.id)
40. 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
41. 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.004..0.005 rows=1 loops=25,351)

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
42. 83.528 186,750.063 ↓ 36.0 36 999

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

43. 8,157.114 186,522.291 ↓ 36.0 36 999

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

44. 13,269.584 100,274.625 ↓ 336.9 19,542 999

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

45. 1,498.500 1,498.500 ↓ 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.044..1.500 rows=13 loops=999)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
46. 85,506.541 85,506.541 ↑ 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.111..6.373 rows=1,455 loops=13,417)

  • Index Cond: (sp_track_id = s.id)
47. 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
48. 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)
49. 2.997 270.729 ↑ 1.0 1 999

Limit (cost=0.56..2.58 rows=1 width=4) (actual time=0.271..0.271 rows=1 loops=999)

50. 267.732 267.732 ↑ 1.0 1 999

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl (cost=0.56..2.58 rows=1 width=4) (actual time=0.268..0.268 rows=1 loops=999)

  • Index Cond: ((occ_chart_edition_id = 132) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
51. 76.923 7,531.461 ↑ 1.0 1 999

Aggregate (cost=186.23..186.24 rows=1 width=4) (actual time=7.538..7.539 rows=1 loops=999)

52. 7,454.538 7,454.538 ↑ 1.7 91 999

Index Scan using index_occ_chart_listings_on_isrc on occ_chart_listings ocl_1 (cost=0.56..185.84 rows=155 width=4) (actual time=0.942..7.462 rows=91 loops=999)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{1,100,101,102,103,104,105,114,115,116,117,118,14,15,16,17,122,123,124,125,132,130,128,126,99,107,108,6,7,18,111,112,13,110,113,121,127,129,131,133,9,10,11,12,8,2,3,109,59,84,95,20,21,22,4,5,28,29,30,26,27,19,31,34,32,33,24,25,35,36,43,23,44,40,41,47,48,49,50,46,60,61,62,45,63,66,67,68,69,70,51,80,81,82,71,75,76,77,54,78,79,86,87,88,89,55,37,56,57,58,83,65,72,73,92,93,94,85,53,52,74,90,91,42,64,38,39,96,97,98}'::bigint[]))
53. 31.968 176.823 ↑ 1.0 1 999

Aggregate (cost=186.23..186.24 rows=1 width=8) (actual time=0.177..0.177 rows=1 loops=999)

54. 144.855 144.855 ↑ 1.7 91 999

Index Scan using index_occ_chart_listings_on_isrc on occ_chart_listings ocl_2 (cost=0.56..185.84 rows=155 width=0) (actual time=0.022..0.145 rows=91 loops=999)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{1,100,101,102,103,104,105,114,115,116,117,118,14,15,16,17,122,123,124,125,132,130,128,126,99,107,108,6,7,18,111,112,13,110,113,121,127,129,131,133,9,10,11,12,8,2,3,109,59,84,95,20,21,22,4,5,28,29,30,26,27,19,31,34,32,33,24,25,35,36,43,23,44,40,41,47,48,49,50,46,60,61,62,45,63,66,67,68,69,70,51,80,81,82,71,75,76,77,54,78,79,86,87,88,89,55,37,56,57,58,83,65,72,73,92,93,94,85,53,52,74,90,91,42,64,38,39,96,97,98}'::bigint[]))
55. 2.997 18.981 ↑ 1.0 1 999

Limit (cost=0.56..2.58 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=999)

56. 15.984 15.984 ↑ 1.0 1 999

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_3 (cost=0.56..2.58 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=999)

  • Index Cond: ((occ_chart_edition_id = 132) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
57. 39.960 165.834 ↑ 1.0 1 999

Aggregate (cost=186.23..186.24 rows=1 width=4) (actual time=0.166..0.166 rows=1 loops=999)

58. 125.874 125.874 ↑ 1.7 91 999

Index Scan using index_occ_chart_listings_on_isrc on occ_chart_listings ocl_4 (cost=0.56..185.84 rows=155 width=4) (actual time=0.016..0.126 rows=91 loops=999)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{1,100,101,102,103,104,105,114,115,116,117,118,14,15,16,17,122,123,124,125,132,130,128,126,99,107,108,6,7,18,111,112,13,110,113,121,127,129,131,133,9,10,11,12,8,2,3,109,59,84,95,20,21,22,4,5,28,29,30,26,27,19,31,34,32,33,24,25,35,36,43,23,44,40,41,47,48,49,50,46,60,61,62,45,63,66,67,68,69,70,51,80,81,82,71,75,76,77,54,78,79,86,87,88,89,55,37,56,57,58,83,65,72,73,92,93,94,85,53,52,74,90,91,42,64,38,39,96,97,98}'::bigint[]))
59. 1.998 14.985 ↑ 1.0 1 999

Limit (cost=0.56..2.58 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=999)

60. 12.987 12.987 ↑ 1.0 1 999

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_5 (cost=0.56..2.58 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=999)

  • Index Cond: ((occ_chart_edition_id = 132) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
61. 2.997 12.987 ↑ 1.0 1 999

Limit (cost=0.56..2.58 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=999)

62. 9.990 9.990 ↑ 1.0 1 999

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_6 (cost=0.56..2.58 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=999)

  • Index Cond: ((occ_chart_edition_id = 132) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
63. 29.970 154.845 ↑ 1.0 1 999

Aggregate (cost=186.23..186.24 rows=1 width=8) (actual time=0.155..0.155 rows=1 loops=999)

64. 124.875 124.875 ↑ 1.7 91 999

Index Scan using index_occ_chart_listings_on_isrc on occ_chart_listings ocl_7 (cost=0.56..185.84 rows=155 width=0) (actual time=0.016..0.125 rows=91 loops=999)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{1,100,101,102,103,104,105,114,115,116,117,118,14,15,16,17,122,123,124,125,132,130,128,126,99,107,108,6,7,18,111,112,13,110,113,121,127,129,131,133,9,10,11,12,8,2,3,109,59,84,95,20,21,22,4,5,28,29,30,26,27,19,31,34,32,33,24,25,35,36,43,23,44,40,41,47,48,49,50,46,60,61,62,45,63,66,67,68,69,70,51,80,81,82,71,75,76,77,54,78,79,86,87,88,89,55,37,56,57,58,83,65,72,73,92,93,94,85,53,52,74,90,91,42,64,38,39,96,97,98}'::bigint[]))
65. 2.967 13.846 ↑ 1.0 1 989

Limit (cost=0.56..2.58 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=989)

66. 10.879 10.879 ↑ 1.0 1 989

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_8 (cost=0.56..2.58 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=989)

  • Index Cond: ((occ_chart_edition_id = 132) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
67. 30.969 155.844 ↑ 1.0 1 999

Aggregate (cost=186.23..186.24 rows=1 width=8) (actual time=0.156..0.156 rows=1 loops=999)

68. 124.875 124.875 ↑ 1.7 91 999

Index Scan using index_occ_chart_listings_on_isrc on occ_chart_listings ocl_9 (cost=0.56..185.84 rows=155 width=0) (actual time=0.016..0.125 rows=91 loops=999)

  • Index Cond: ((primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
  • Filter: (occ_chart_edition_id = ANY ('{1,100,101,102,103,104,105,114,115,116,117,118,14,15,16,17,122,123,124,125,132,130,128,126,99,107,108,6,7,18,111,112,13,110,113,121,127,129,131,133,9,10,11,12,8,2,3,109,59,84,95,20,21,22,4,5,28,29,30,26,27,19,31,34,32,33,24,25,35,36,43,23,44,40,41,47,48,49,50,46,60,61,62,45,63,66,67,68,69,70,51,80,81,82,71,75,76,77,54,78,79,86,87,88,89,55,37,56,57,58,83,65,72,73,92,93,94,85,53,52,74,90,91,42,64,38,39,96,97,98}'::bigint[]))
69. 1.978 13.846 ↑ 1.0 1 989

Limit (cost=0.56..2.58 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=989)

70. 11.868 11.868 ↑ 1.0 1 989

Index Scan using index_occ_chart_listings_on_edition_id_and_isrc on occ_chart_listings ocl_10 (cost=0.56..2.58 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=989)

  • Index Cond: ((occ_chart_edition_id = 132) AND (primary_isrc IS NOT NULL) AND ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text))
Planning time : 11.166 ms
Execution time : 422,663.249 ms