explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qEtd

Settings
# exclusive inclusive rows x rows loops node
1. 52.763 615,323.558 ↓ 3.6 1,000 1

Nested Loop Left Join (cost=3.70..6,370,103.01 rows=277 width=921) (actual time=2,939.101..615,323.558 rows=1,000 loops=1)

2. 4.761 2,165.795 ↓ 3.6 1,000 1

Nested Loop Left Join (cost=3.27..1,061.47 rows=277 width=858) (actual time=3.763..2,165.795 rows=1,000 loops=1)

3. 4.945 1,917.034 ↓ 3.6 1,000 1

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

4. 5.035 1,360.089 ↓ 3.6 1,000 1

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

5. 4.988 1,094.054 ↓ 3.6 1,000 1

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

6. 4.590 530.066 ↓ 3.6 1,000 1

Nested Loop (cost=1.55..354.49 rows=277 width=80) (actual time=1.571..530.066 rows=1,000 loops=1)

7. 1.481 6.476 ↓ 3.6 1,000 1

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

8. 0.006 0.451 ↑ 1.0 1 1

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

9. 0.411 0.411 ↑ 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=0.025..0.411 rows=1 loops=1)

  • Index Cond: (shazam_chart_source_id = 1)
10. 0.034 0.034 ↑ 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.032..0.034 rows=1 loops=1)

  • Index Cond: (id = 1)
  • Heap Fetches: 1
11. 4.544 4.544 ↑ 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=0.488..4.544 rows=1,000 loops=1)

  • Index Cond: (shazam_chart_edition_id = shazam_chart_editions.id)
12. 519.000 519.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.104..0.519 rows=1 loops=1,000)

  • Index Cond: (id = shazam_chart_listings.shazam_track_id)
13. 559.000 559.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=0.554..0.559 rows=1 loops=1,000)

  • Index Cond: (shazam_track_id = shazam_tracks.id)
14. 261.000 261.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.252..0.261 rows=1 loops=1,000)

  • Index Cond: (id = shazam_tracks.it_song_id)
15. 552.000 552.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.539..0.552 rows=1 loops=1,000)

  • Index Cond: (id = it_songs.it_collection_id)
16. 244.000 244.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.241..0.244 rows=1 loops=1,000)

  • Index Cond: (id = it_songs.it_account_id)
17. 69.000 69.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.020..0.069 rows=1 loops=1,000)

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

SubPlan (for Nested Loop Left Join)

19. 60.000 408,865.000 ↑ 1.0 1 1,000

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

20. 10,474.308 408,805.000 ↓ 24.0 72 1,000

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

  • Hash Cond: (shazam_chart_listings_1.shazam_chart_edition_id = shazam_chart_editions_1.id)
21. 398,304.000 398,304.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=0.956..398.304 rows=29,389 loops=1,000)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
23. 26.506 26.506 ↓ 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.027..26.506 rows=318 loops=1)

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

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

25. 9,347.522 50,525.000 ↓ 24.0 72 1,000

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

  • Hash Cond: (shazam_chart_listings_2.shazam_chart_edition_id = shazam_chart_editions_2.id)
26. 41,177.000 41,177.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.020..41.177 rows=29,389 loops=1,000)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
28. 0.337 0.337 ↓ 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.028..0.337 rows=318 loops=1)

  • Index Cond: (shazam_chart_source_id = 1)
29. 112.000 51,818.000 ↑ 1.0 1 1,000

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

30. 341.461 51,706.000 ↓ 29.8 357 1,000

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

31. 9,898.725 50,295.000 ↓ 29.8 357 1,000

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

  • Hash Cond: (shazam_chart_listings_3.shazam_chart_edition_id = shazam_chart_editions_3.id)
32. 40,228.000 40,228.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.018..40.228 rows=29,389 loops=1,000)

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
34. 166.986 166.986 ↓ 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.012..166.986 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. 107.000 51,248.000 ↑ 1.0 1 1,000

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

37. 270.461 51,141.000 ↓ 27.9 335 1,000

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

38. 9,808.383 49,801.000 ↓ 29.8 357 1,000

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

  • Hash Cond: (shazam_chart_listings_4.shazam_chart_edition_id = shazam_chart_editions_4.id)
39. 39,990.000 39,990.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.017..39.990 rows=29,389 loops=1,000)

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
41. 2.070 2.070 ↓ 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.070 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 50,541.000 ↑ 1.0 1 1,000

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

44. 368.974 50,529.000 ↓ 20.0 20 1,000

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

45. 9,641.463 49,447.000 ↓ 29.8 357 1,000

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

  • Hash Cond: (shazam_chart_listings_5.shazam_chart_edition_id = shazam_chart_editions_5.id)
46. 39,803.000 39,803.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..39.803 rows=29,389 loops=1,000)

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
48. 2.008 2.008 ↓ 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.007..2.008 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 : 5.982 ms
Execution time : 615,324.658 ms