explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OtG5

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 329.421 ↑ 1.0 1 1

Aggregate (cost=26,810.65..26,810.66 rows=1 width=8) (actual time=329.421..329.421 rows=1 loops=1)

2. 0.058 329.414 ↑ 3,755.0 1 1

HashAggregate (cost=26,726.16..26,763.71 rows=3,755 width=8) (actual time=329.403..329.414 rows=1 loops=1)

  • Group Key: sp_playlists.id, sp_playlists.followers_count
3. 0.016 329.356 ↑ 1,877.5 2 1

Append (cost=0.99..26,707.39 rows=3,755 width=8) (actual time=0.771..329.356 rows=2 loops=1)

4. 0.003 0.771 ↑ 5.0 1 1

Nested Loop (cost=0.99..58.66 rows=5 width=8) (actual time=0.770..0.771 rows=1 loops=1)

5. 0.004 0.760 ↑ 5.0 1 1

Nested Loop (cost=0.71..55.68 rows=5 width=4) (actual time=0.759..0.760 rows=1 loops=1)

6. 0.743 0.743 ↑ 5.0 1 1

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.742..0.743 rows=1 loops=1)

  • Index Cond: (sp_track_id = 684,091)
7. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: (id = sp_playlist_listings.sp_playlist_edition_id)
  • Filter: current
8. 0.008 0.008 ↑ 1.0 1 1

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

  • Index Cond: (id = sp_playlist_editions.sp_playlist_id)
9. 0.030 328.569 ↑ 3,750.0 1 1

Hash Join (cost=6,516.83..26,611.18 rows=3,750 width=8) (actual time=165.389..328.569 rows=1 loops=1)

  • Hash Cond: (sp_playlist_editions_1.sp_playlist_id = sp_playlists_1.id)
10. 0.019 318.545 ↑ 3,750.0 1 1

Hash Join (cost=5,823.30..25,907.81 rows=3,750 width=4) (actual time=155.366..318.545 rows=1 loops=1)

  • Hash Cond: (sp_playlist_listings_1.sp_playlist_edition_id = sp_playlist_editions_1.id)
11. 184.526 313.943 ↑ 3,876.0 1 1

Hash Join (cost=5,594.35..25,668.67 rows=3,876 width=4) (actual time=150.764..313.943 rows=1 loops=1)

  • Hash Cond: (sp_playlist_listings_1.sp_track_id = s.id)
12. 129.339 129.339 ↑ 1.0 776,103 1

Seq Scan on sp_playlist_listings sp_playlist_listings_1 (cost=0.00..18,037.03 rows=776,103 width=8) (actual time=0.013..129.339 rows=776,103 loops=1)

13. 0.009 0.078 ↑ 2,014.0 1 1

Hash (cost=5,569.17..5,569.17 rows=2,014 width=4) (actual time=0.078..0.078 rows=1 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
14. 0.013 0.069 ↑ 2,014.0 1 1

Bitmap Heap Scan on sp_tracks s (cost=48.05..5,569.17 rows=2,014 width=4) (actual time=0.069..0.069 rows=1 loops=1)

  • Recheck Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = 'GBJXP1421202'::citext)
  • Filter: (isrc IS NOT NULL)
  • Heap Blocks: exact=1
15. 0.056 0.056 ↑ 2,016.0 1 1

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

  • Index Cond: ((upper(replace((isrc)::text, '-'::text, ''::text)))::citext = 'GBJXP1421202'::citext)
16. 2.036 4.583 ↑ 1.0 6,829 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 331kB
17. 2.547 2.547 ↑ 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.024..2.547 rows=6,829 loops=1)

  • Filter: current
  • Rows Removed by Filter: 230
18. 3.751 9.994 ↑ 1.0 9,890 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 503kB
19. 6.243 6.243 ↑ 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..6.243 rows=9,890 loops=1)

Planning time : 2.625 ms
Execution time : 330.158 ms