explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jemo

Settings
# exclusive inclusive rows x rows loops node
1. 16.541 253,867.671 ↓ 999.0 999 1

Nested Loop Left Join (cost=5,603.25..32,557.12 rows=1 width=1,917) (actual time=383.154..253,867.671 rows=999 loops=1)

2. 3.259 1,507.726 ↓ 999.0 999 1

Nested Loop Left Join (cost=5,602.83..5,631.70 rows=1 width=2,121) (actual time=48.728..1,507.726 rows=999 loops=1)

3. 2.644 1,388.583 ↓ 999.0 999 1

Nested Loop Left Join (cost=5,602.41..5,631.13 rows=1 width=1,926) (actual time=48.382..1,388.583 rows=999 loops=1)

4. 3.531 1,351.973 ↓ 999.0 999 1

Nested Loop Left Join (cost=5,601.98..5,630.62 rows=1 width=1,922) (actual time=48.339..1,351.973 rows=999 loops=1)

  • Join Filter: (it_accounts.id = it_collections.it_account_id)
  • Rows Removed by Join Filter: 14
5. 2.602 1,304.486 ↓ 999.0 999 1

Nested Loop Left Join (cost=5,601.69..5,630.21 rows=1 width=1,772) (actual time=48.323..1,304.486 rows=999 loops=1)

6. 77.874 1,273.912 ↓ 999.0 999 1

Nested Loop Left Join (cost=5,601.41..5,629.88 rows=1 width=1,768) (actual time=48.306..1,273.912 rows=999 loops=1)

7. 68.056 1,190.044 ↓ 999.0 999 1

Nested Loop Left Join (cost=21.74..42.19 rows=1 width=1,635) (actual time=48.145..1,190.044 rows=999 loops=1)

8. 3.227 1,116.993 ↓ 999.0 999 1

Nested Loop (cost=1.10..13.54 rows=1 width=1,393) (actual time=48.028..1,116.993 rows=999 loops=1)

  • Join Filter: (occ_chart_editions.occ_chart_source_id = occ_chart_sources.id)
9. 4.338 1,108.771 ↓ 999.0 999 1

Nested Loop (cost=0.98..13.39 rows=1 width=1,409) (actual time=47.979..1,108.771 rows=999 loops=1)

10. 6.594 1,086.451 ↓ 999.0 999 1

Nested Loop (cost=0.84..12.94 rows=1 width=1,401) (actual time=47.492..1,086.451 rows=999 loops=1)

11. 50.887 50.887 ↓ 999.0 999 1

Index Scan using index_occ_chart_listings_on_occ_chart_edition_id on occ_chart_listings (cost=0.42..4.48 rows=1 width=96) (actual time=46.566..50.887 rows=999 loops=1)

  • Index Cond: (occ_chart_edition_id = 17)
  • Filter: (("position" >= 0) AND ("position" <= 999))
  • Rows Removed by Filter: 49,001
12. 1,028.970 1,028.970 ↑ 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..8.45 rows=1 width=1,318) (actual time=0.885..1.030 rows=1 loops=999)

  • Index Cond: ((primary_isrc)::text = (occ_chart_listings.primary_isrc)::text)
13. 17.982 17.982 ↑ 1.0 1 999

Index Scan using index_occ_chart_editions_on_occ_chart_source_id_and_week_id on occ_chart_editions (cost=0.14..0.44 rows=1 width=16) (actual time=0.018..0.018 rows=1 loops=999)

  • Index Cond: (occ_chart_source_id = occ_processed_chart_listings.occ_chart_source_id)
  • Filter: (id = 17)
  • Rows Removed by Filter: 14
14. 4.995 4.995 ↑ 1.0 1 999

Index Only Scan using occ_chart_sources_pkey on occ_chart_sources (cost=0.12..0.14 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=999)

  • Index Cond: (id = occ_processed_chart_listings.occ_chart_source_id)
  • Heap Fetches: 999
15. 0.000 4.995 ↑ 1.0 1 999

Index Scan using it_songs_pkey on it_songs (cost=20.63..28.65 rows=1 width=242) (actual time=0.005..0.005 rows=1 loops=999)

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

SubPlan (for Index Scan)

17. 4.995 60.939 ↑ 1.0 1 999

Limit (cost=20.21..20.22 rows=1 width=8) (actual time=0.060..0.061 rows=1 loops=999)

18. 16.983 55.944 ↑ 4.0 1 999

Sort (cost=20.21..20.22 rows=4 width=8) (actual time=0.056..0.056 rows=1 loops=999)

  • Sort Key: (char_length((it_songs_2.composer_names)::text)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
19. 6.993 38.961 ↑ 4.0 1 999

Bitmap Heap Scan on it_songs it_songs_2 (cost=4.46..20.19 rows=4 width=8) (actual time=0.038..0.039 rows=1 loops=999)

  • Recheck Cond: ((isrc IS NOT NULL) AND ((isrc)::text = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::text))
  • Heap Blocks: exact=631
20. 31.968 31.968 ↑ 4.0 1 999

Bitmap Index Scan on index_it_songs_on_isrc (cost=0.00..4.46 rows=4 width=0) (actual time=0.032..0.032 rows=1 loops=999)

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

Index Scan using sp_tracks_pkey on sp_tracks (cost=5,579.67..5,587.69 rows=1 width=133) (actual time=0.006..0.006 rows=1 loops=999)

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

SubPlan (for Index Scan)

23. 2.997 71.928 ↑ 1.0 1 999

Limit (cost=5,579.24..5,579.25 rows=1 width=8) (actual time=0.071..0.072 rows=1 loops=999)

24. 11.988 68.931 ↑ 2,014.0 1 999

Sort (cost=5,579.24..5,584.28 rows=2,014 width=8) (actual time=0.069..0.069 rows=1 loops=999)

  • Sort Key: sp_tracks_2.popularity DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
25. 7.992 56.943 ↑ 2,014.0 1 999

Bitmap Heap Scan on sp_tracks sp_tracks_2 (cost=48.05..5,569.17 rows=2,014 width=8) (actual time=0.055..0.057 rows=1 loops=999)

  • Recheck Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (COALESCE(occ_chart_listings.expected_isrc, occ_chart_listings.primary_isrc))::citext)
  • Filter: (isrc IS NOT NULL)
  • Heap Blocks: exact=1,213
26. 48.951 48.951 ↑ 2,016.0 1 999

Bitmap Index Scan on index_sp_tracks_on_sanitized_isrc (cost=0.00..47.54 rows=2,016 width=0) (actual time=0.049..0.049 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)
27. 27.972 27.972 ↑ 1.0 1 999

Index Scan using it_accounts_pkey on it_accounts (cost=0.29..0.33 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=999)

  • Index Cond: (it_songs.it_account_id = id)
28. 43.956 43.956 ↑ 1.0 1 999

Index Scan using it_collections_pkey on it_collections (cost=0.29..0.40 rows=1 width=162) (actual time=0.044..0.044 rows=1 loops=999)

  • Index Cond: (id = it_songs.it_collection_id)
29. 33.966 33.966 ↑ 1.0 1 999

Index Scan using index_sp_account_tracks_on_sp_track_id_and_primary on sp_account_tracks (cost=0.42..0.50 rows=1 width=8) (actual time=0.032..0.034 rows=1 loops=999)

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

Index Scan using sp_accounts_pkey on sp_accounts (cost=0.42..0.57 rows=1 width=199) (actual time=0.116..0.116 rows=1 loops=999)

  • Index Cond: (id = sp_account_tracks.sp_account_id)
31. 45.954 45.954 ↓ 0.0 0 999

Index Scan using artists_pkey on artists (cost=0.42..1.27 rows=1 width=353) (actual time=0.046..0.046 rows=0 loops=999)

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

SubPlan (for Nested Loop Left Join)

33. 115.884 329.670 ↑ 1.0 1 999

Aggregate (cost=50.34..50.35 rows=1 width=8) (actual time=0.330..0.330 rows=1 loops=999)

34. 28.701 213.786 ↓ 4.2 17 999

Nested Loop (cost=0.85..50.33 rows=4 width=36) (actual time=0.026..0.214 rows=17 loops=999)

35. 80.919 80.919 ↓ 4.2 17 999

Index Scan using index_sp_account_tracks_on_sp_account_id on sp_account_tracks sp_account_tracks_1 (cost=0.42..16.57 rows=4 width=4) (actual time=0.017..0.081 rows=17 loops=999)

  • Index Cond: (sp_account_id = sp_accounts.id)
  • Filter: ("primary" AND (NOT inactive))
  • Rows Removed by Filter: 5
36. 104.166 104.166 ↑ 1.0 1 17,361

Index Scan using sp_tracks_pkey on sp_tracks sp_tracks_1 (cost=0.42..8.44 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=17,361)

  • Index Cond: (id = sp_account_tracks_1.sp_track_id)
37. 148.851 197.802 ↑ 1.0 1 999

Aggregate (cost=53.03..53.04 rows=1 width=8) (actual time=0.198..0.198 rows=1 loops=999)

38. 48.951 48.951 ↓ 2.1 30 999

Index Scan using index_it_songs_on_it_account_id on it_songs it_songs_1 (cost=0.42..52.89 rows=14 width=17) (actual time=0.007..0.049 rows=30 loops=999)

  • Index Cond: (it_account_id = it_accounts.id)
39. 18.981 251,769.978 ↑ 1.0 1 999

Aggregate (cost=26,820.74..26,820.75 rows=1 width=8) (actual time=252.022..252.022 rows=1 loops=999)

40. 72.927 251,750.997 ↑ 469.4 8 999

HashAggregate (cost=26,736.25..26,773.80 rows=3,755 width=8) (actual time=251.972..252.003 rows=8 loops=999)

  • Group Key: sp_playlists.id, sp_playlists.followers_count
41. 5.994 251,678.070 ↑ 268.2 14 999

Append (cost=0.99..26,717.47 rows=3,755 width=8) (actual time=2.724..251.930 rows=14 loops=999)

42. 8.135 207.792 ↓ 1.2 6 999

Nested Loop (cost=0.99..58.66 rows=5 width=8) (actual time=0.146..0.208 rows=6 loops=999)

43. 10.125 174.825 ↓ 1.2 6 999

Nested Loop (cost=0.71..55.68 rows=5 width=4) (actual time=0.138..0.175 rows=6 loops=999)

44. 139.860 139.860 ↓ 1.2 6 999

Index Scan using index_sp_playlist_listings_on_sp_track_id on sp_playlist_listings (cost=0.42..14.18 rows=5 width=4) (actual time=0.130..0.140 rows=6 loops=999)

  • Index Cond: (sp_track_id = sp_tracks.id)
45. 24.840 24.840 ↑ 1.0 1 6,210

Index Scan using sp_playlist_editions_pkey on sp_playlist_editions (cost=0.28..8.30 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=6,210)

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
  • Rows Removed by Filter: 0
46. 24.832 24.832 ↑ 1.0 1 6,208

Index Scan using sp_playlists_pkey on sp_playlists (cost=0.29..0.60 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=6,208)

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
47. 13.063 251,464.284 ↑ 468.8 8 999

Hash Join (cost=6,526.91..26,621.26 rows=3,750 width=8) (actual time=50.331..251.716 rows=8 loops=999)

  • Hash Cond: (sp_playlist_editions_1.sp_playlist_id = sp_playlists_1.id)
48. 16.952 251,443.305 ↑ 468.8 8 999

Hash Join (cost=5,833.39..25,917.89 rows=3,750 width=4) (actual time=50.320..251.695 rows=8 loops=999)

  • Hash Cond: (sp_playlist_listings_1.sp_playlist_edition_id = sp_playlist_editions_1.id)
49. 150,197.399 251,422.326 ↑ 484.5 8 999

Hash Join (cost=5,604.43..25,678.76 rows=3,876 width=4) (actual time=50.308..251.674 rows=8 loops=999)

  • Hash Cond: (sp_playlist_listings_1.sp_track_id = s.id)
50. 101,179.972 101,179.972 ↑ 1.0 776,103 709

Seq Scan on sp_playlist_listings sp_playlist_listings_1 (cost=0.00..18,037.03 rows=776,103 width=8) (actual time=0.005..142.708 rows=776,103 loops=709)

51. 4.995 44.955 ↑ 2,014.0 1 999

Hash (cost=5,579.26..5,579.26 rows=2,014 width=4) (actual time=0.045..0.045 rows=1 loops=999)

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
52. 6.993 39.960 ↑ 2,014.0 1 999

Bitmap Heap Scan on sp_tracks s (cost=48.05..5,579.26 rows=2,014 width=4) (actual time=0.039..0.040 rows=1 loops=999)

  • Recheck Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
  • Filter: (isrc IS NOT NULL)
  • Heap Blocks: exact=1,213
53. 32.967 32.967 ↑ 2,016.0 1 999

Bitmap Index Scan on index_sp_tracks_on_sanitized_isrc (cost=0.00..47.55 rows=2,016 width=0) (actual time=0.033..0.033 rows=1 loops=999)

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = (upper(replace((sp_tracks.isrc)::text, '-'::text, ''::text)))::citext)
54. 1.803 4.027 ↑ 1.0 6,829 1

Hash (cost=143.59..143.59 rows=6,829 width=8) (actual time=4.027..4.027 rows=6,829 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 331kB
55. 2.224 2.224 ↑ 1.0 6,829 1

Seq Scan on sp_playlist_editions sp_playlist_editions_1 (cost=0.00..143.59 rows=6,829 width=8) (actual time=0.023..2.224 rows=6,829 loops=1)

  • Filter: current
  • Rows Removed by Filter: 230
56. 2.717 7.916 ↑ 1.0 9,890 1

Hash (cost=569.90..569.90 rows=9,890 width=8) (actual time=7.916..7.916 rows=9,890 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 503kB
57. 5.199 5.199 ↑ 1.0 9,890 1

Seq Scan on sp_playlists sp_playlists_1 (cost=0.00..569.90 rows=9,890 width=8) (actual time=0.013..5.199 rows=9,890 loops=1)

Planning time : 29.513 ms
Execution time : 253,870.478 ms