explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q6yK

Settings
# exclusive inclusive rows x rows loops node
1. 54.272 812,567.404 ↓ 3.6 1,000 1

Nested Loop Left Join (cost=3.70..6,370,103.01 rows=277 width=921) (actual time=14,317.967..812,567.404 rows=1,000 loops=1)

2. 5.645 9,107.132 ↓ 3.6 1,000 1

Nested Loop Left Join (cost=3.27..1,061.47 rows=277 width=858) (actual time=9.107..9,107.132 rows=1,000 loops=1)

3. 5.975 8,830.487 ↓ 3.6 1,000 1

Nested Loop Left Join (cost=2.85..933.30 rows=277 width=841) (actual time=8.656..8,830.487 rows=1,000 loops=1)

4. 6.294 7,910.512 ↓ 3.6 1,000 1

Nested Loop Left Join (cost=2.41..799.67 rows=277 width=671) (actual time=5.824..7,910.512 rows=1,000 loops=1)

5. 6.317 7,468.218 ↓ 3.6 1,000 1

Nested Loop Left Join (cost=1.98..518.16 rows=277 width=449) (actual time=5.404..7,468.218 rows=1,000 loops=1)

6. 5.622 5,912.901 ↓ 3.6 1,000 1

Nested Loop (cost=1.55..354.49 rows=277 width=80) (actual time=4.050..5,912.901 rows=1,000 loops=1)

7. 1.783 26.279 ↓ 3.6 1,000 1

Nested Loop (cost=1.12..228.27 rows=277 width=25) (actual time=3.196..26.279 rows=1,000 loops=1)

8. 0.005 2.073 ↑ 1.0 1 1

Nested Loop (cost=0.56..56.32 rows=1 width=21) (actual time=1.593..2.073 rows=1 loops=1)

9. 1.567 1.567 ↑ 1.0 1 1

Index Scan using idx_shazam_chart_editions_current_source on shazam_chart_editions (cost=0.28..55.01 rows=1 width=21) (actual time=1.093..1.567 rows=1 loops=1)

  • Index Cond: (shazam_chart_source_id = 1)
10. 0.501 0.501 ↑ 1.0 1 1

Index Only Scan using shazam_chart_sources_pkey on shazam_chart_sources (cost=0.28..1.30 rows=1 width=8) (actual time=0.497..0.501 rows=1 loops=1)

  • Index Cond: (id = 1)
  • Heap Fetches: 1
11. 22.423 22.423 ↑ 4.2 1,000 1

Index Scan using index_shazam_chart_listings_on_shazam_chart_edition_id on shazam_chart_listings (cost=0.57..129.86 rows=4,209 width=20) (actual time=1.598..22.423 rows=1,000 loops=1)

  • Index Cond: (shazam_chart_edition_id = shazam_chart_editions.id)
12. 5,881.000 5,881.000 ↑ 1.0 1 1,000

Index Scan using shazam_tracks_pkey on shazam_tracks (cost=0.43..0.45 rows=1 width=63) (actual time=0.583..5.881 rows=1 loops=1,000)

  • Index Cond: (id = shazam_chart_listings.shazam_track_id)
13. 1,549.000 1,549.000 ↑ 1.0 1 1,000

Index Scan using index_shazam_track_growth_metrics_on_shazam_track_id on shazam_track_growth_metrics (cost=0.43..0.58 rows=1 width=377) (actual time=1.445..1.549 rows=1 loops=1,000)

  • Index Cond: (shazam_track_id = shazam_tracks.id)
14. 436.000 436.000 ↑ 1.0 1 1,000

Index Scan using it_songs_pkey on it_songs (cost=0.43..1.01 rows=1 width=230) (actual time=0.413..0.436 rows=1 loops=1,000)

  • Index Cond: (id = shazam_tracks.it_song_id)
15. 914.000 914.000 ↑ 1.0 1 1,000

Index Scan using it_collections_pkey on it_collections (cost=0.43..0.47 rows=1 width=178) (actual time=0.881..0.914 rows=1 loops=1,000)

  • Index Cond: (id = it_songs.it_collection_id)
16. 271.000 271.000 ↑ 1.0 1 1,000

Index Scan using it_accounts_pkey on it_accounts (cost=0.43..0.45 rows=1 width=21) (actual time=0.266..0.271 rows=1 loops=1,000)

  • Index Cond: (id = it_songs.it_account_id)
17. 96.000 96.000 ↑ 1.0 1 1,000

Index Scan using artists_pkey on artists (cost=0.43..0.57 rows=1 width=27) (actual time=0.082..0.096 rows=1 loops=1,000)

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

SubPlan (for Nested Loop Left Join)

19. 65.000 595,988.000 ↑ 1.0 1 1,000

Aggregate (cost=4,345.72..4,345.73 rows=1 width=8) (actual time=595.987..595.988 rows=1 loops=1,000)

20. 10,647.161 595,923.000 ↓ 24.0 72 1,000

Hash Join (cost=312.92..4,345.71 rows=3 width=8) (actual time=2.782..595.923 rows=72 loops=1,000)

  • Hash Cond: (shazam_chart_listings_1.shazam_chart_edition_id = shazam_chart_editions_1.id)
21. 585,260.000 585,260.000 ↓ 7.2 29,389 1,000

Index Scan using index_shazam_chart_listings_on_shazam_track_id on shazam_chart_listings shazam_chart_listings_1 (cost=0.57..4,018.08 rows=4,064 width=16) (actual time=1.128..585.260 rows=29,389 loops=1,000)

  • Index Cond: (shazam_track_id = shazam_tracks.id)
22. 0.159 15.839 ↓ 1.1 318 1

Hash (cost=308.63..308.63 rows=298 width=8) (actual time=15.839..15.839 rows=318 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
23. 15.680 15.680 ↓ 1.1 318 1

Index Scan using index_shazam_chart_editions_on_shazam_chart_source_id on shazam_chart_editions shazam_chart_editions_1 (cost=0.42..308.63 rows=298 width=8) (actual time=0.021..15.680 rows=318 loops=1)

  • Index Cond: (shazam_chart_source_id = 1)
24. 40.000 50,962.000 ↑ 1.0 1 1,000

Aggregate (cost=4,345.72..4,345.73 rows=1 width=4) (actual time=50.962..50.962 rows=1 loops=1,000)

25. 9,370.408 50,922.000 ↓ 24.0 72 1,000

Hash Join (cost=312.92..4,345.71 rows=3 width=4) (actual time=0.192..50.922 rows=72 loops=1,000)

  • Hash Cond: (shazam_chart_listings_2.shazam_chart_edition_id = shazam_chart_editions_2.id)
26. 41,551.000 41,551.000 ↓ 7.2 29,389 1,000

Index Scan using index_shazam_chart_listings_on_shazam_track_id on shazam_chart_listings shazam_chart_listings_2 (cost=0.57..4,018.08 rows=4,064 width=12) (actual time=0.021..41.551 rows=29,389 loops=1,000)

  • Index Cond: (shazam_track_id = shazam_tracks.id)
27. 0.176 0.592 ↓ 1.1 318 1

Hash (cost=308.63..308.63 rows=298 width=8) (actual time=0.592..0.592 rows=318 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
28. 0.416 0.416 ↓ 1.1 318 1

Index Scan using index_shazam_chart_editions_on_shazam_chart_source_id on shazam_chart_editions shazam_chart_editions_2 (cost=0.42..308.63 rows=298 width=8) (actual time=0.018..0.416 rows=318 loops=1)

  • Index Cond: (shazam_chart_source_id = 1)
29. 116.000 52,912.000 ↑ 1.0 1 1,000

Aggregate (cost=4,766.41..4,766.42 rows=1 width=8) (actual time=52.912..52.912 rows=1 loops=1,000)

30. 359.461 52,796.000 ↓ 29.8 357 1,000

Nested Loop (cost=729.93..4,766.38 rows=12 width=0) (actual time=0.345..52.796 rows=357 loops=1,000)

31. 10,047.054 51,367.000 ↓ 29.8 357 1,000

Hash Join (cost=729.65..4,762.53 rows=12 width=8) (actual time=0.329..51.367 rows=357 loops=1,000)

  • Hash Cond: (shazam_chart_listings_3.shazam_chart_edition_id = shazam_chart_editions_3.id)
32. 41,032.000 41,032.000 ↓ 7.2 29,389 1,000

Index Scan using index_shazam_chart_listings_on_shazam_track_id on shazam_chart_listings shazam_chart_listings_3 (cost=0.57..4,018.08 rows=4,064 width=8) (actual time=0.020..41.032 rows=29,389 loops=1,000)

  • Index Cond: (shazam_track_id = shazam_tracks.id)
33. 1.317 287.946 ↓ 1.1 1,533 1

Hash (cost=711.21..711.21 rows=1,430 width=16) (actual time=287.946..287.946 rows=1,533 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
34. 286.629 286.629 ↓ 1.1 1,533 1

Index Scan using idx_shazam_chart_editions_current_source on shazam_chart_editions shazam_chart_editions_3 (cost=0.28..711.21 rows=1,430 width=16) (actual time=0.014..286.629 rows=1,533 loops=1)

35. 1,069.539 1,069.539 ↑ 1.0 1 356,513

Index Only Scan using shazam_chart_sources_pkey on shazam_chart_sources shazam_chart_sources_1 (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=356,513)

  • Index Cond: (id = shazam_chart_editions_3.shazam_chart_source_id)
  • Heap Fetches: 356,513
36. 108.000 52,091.000 ↑ 1.0 1 1,000

Aggregate (cost=4,767.23..4,767.24 rows=1 width=8) (actual time=52.090..52.091 rows=1 loops=1,000)

37. 281.461 51,983.000 ↓ 27.9 335 1,000

Nested Loop (cost=729.93..4,767.20 rows=12 width=0) (actual time=0.067..51.983 rows=335 loops=1,000)

38. 9,858.786 50,632.000 ↓ 29.8 357 1,000

Hash Join (cost=729.65..4,762.53 rows=12 width=8) (actual time=0.042..50.632 rows=357 loops=1,000)

  • Hash Cond: (shazam_chart_listings_4.shazam_chart_edition_id = shazam_chart_editions_4.id)
39. 40,770.000 40,770.000 ↓ 7.2 29,389 1,000

Index Scan using index_shazam_chart_listings_on_shazam_track_id on shazam_chart_listings shazam_chart_listings_4 (cost=0.57..4,018.08 rows=4,064 width=8) (actual time=0.019..40.770 rows=29,389 loops=1,000)

  • Index Cond: (shazam_track_id = shazam_tracks.id)
40. 0.781 3.214 ↓ 1.1 1,533 1

Hash (cost=711.21..711.21 rows=1,430 width=16) (actual time=3.214..3.214 rows=1,533 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
41. 2.433 2.433 ↓ 1.1 1,533 1

Index Scan using idx_shazam_chart_editions_current_source on shazam_chart_editions shazam_chart_editions_4 (cost=0.28..711.21 rows=1,430 width=16) (actual time=0.008..2.433 rows=1,533 loops=1)

42. 1,069.539 1,069.539 ↑ 1.0 1 356,513

Index Scan using shazam_chart_sources_pkey on shazam_chart_sources shazam_chart_sources_2 (cost=0.28..0.38 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=356,513)

  • Index Cond: (id = shazam_chart_editions_4.shazam_chart_source_id)
  • Filter: ((shazam_chart_city_id IS NOT NULL) AND (shazam_chart_country_id IS NOT NULL))
  • Rows Removed by Filter: 0
43. 12.000 51,357.000 ↑ 1.0 1 1,000

Aggregate (cost=4,767.21..4,767.22 rows=1 width=8) (actual time=51.356..51.357 rows=1 loops=1,000)

44. 369.974 51,345.000 ↓ 20.0 20 1,000

Nested Loop (cost=729.93..4,767.20 rows=1 width=0) (actual time=0.308..51.345 rows=20 loops=1,000)

45. 9,796.519 50,262.000 ↓ 29.8 357 1,000

Hash Join (cost=729.65..4,762.53 rows=12 width=8) (actual time=0.041..50.262 rows=357 loops=1,000)

  • Hash Cond: (shazam_chart_listings_5.shazam_chart_edition_id = shazam_chart_editions_5.id)
46. 40,463.000 40,463.000 ↓ 7.2 29,389 1,000

Index Scan using index_shazam_chart_listings_on_shazam_track_id on shazam_chart_listings shazam_chart_listings_5 (cost=0.57..4,018.08 rows=4,064 width=8) (actual time=0.018..40.463 rows=29,389 loops=1,000)

  • Index Cond: (shazam_track_id = shazam_tracks.id)
47. 0.540 2.481 ↓ 1.1 1,533 1

Hash (cost=711.21..711.21 rows=1,430 width=16) (actual time=2.481..2.481 rows=1,533 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
48. 1.941 1.941 ↓ 1.1 1,533 1

Index Scan using idx_shazam_chart_editions_current_source on shazam_chart_editions shazam_chart_editions_5 (cost=0.28..711.21 rows=1,430 width=16) (actual time=0.008..1.941 rows=1,533 loops=1)

49. 713.026 713.026 ↓ 0.0 0 356,513

Index Scan using shazam_chart_sources_pkey on shazam_chart_sources shazam_chart_sources_3 (cost=0.28..0.38 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=356,513)

  • Index Cond: (id = shazam_chart_editions_5.shazam_chart_source_id)
  • Filter: ((shazam_chart_city_id IS NULL) AND (shazam_chart_country_id IS NOT NULL))
  • Rows Removed by Filter: 1
Planning time : 16.192 ms
Execution time : 812,568.801 ms