explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iTUE

Settings
# exclusive inclusive rows x rows loops node
1. 0.066 5,017.667 ↑ 114.6 25 1

Sort (cost=330,120.30..330,127.46 rows=2,864 width=366) (actual time=5,017.657..5,017.667 rows=25 loops=1)

  • Sort Key: sounds.id, sound_artists.added_by
  • Sort Method: quicksort Memory: 49kB
2. 0.262 5,017.601 ↑ 114.6 25 1

HashAggregate (cost=329,927.21..329,955.85 rows=2,864 width=366) (actual time=5,017.578..5,017.601 rows=25 loops=1)

  • Group Key: sounds.id, sounds.tiktok_id, (btrim((COALESCE(sounds.name, 'Untitled'::character varying))::text)), sounds.url, (btrim((COALESCE(sounds.author, 'Unnamed'::character varying))::text)), users.url, users.follower_count, sounds.image_url, sounds.preview_url, sounds.post_count, (((sounds.tiktok_user_id IS NOT NULL) OR ((COALESCE(sounds.author, ''::character varying))::text = 'Unknown'::text))), (((sounds.last_refreshed_at IS NULL) OR (sounds.last_refreshed_at <= '2020-08-28 03:54:28'::timestamp without time zone))), metrics.post_count_1_day_delta, metrics.post_count_7_day_delta, metrics.tiktok_sound_week_over_week_growth, metrics.minigraph, (NULL::character varying), (NULL::character varying), (NULL::character varying), sound_artists.added_by
3. 0.027 5,017.339 ↑ 114.6 25 1

Append (cost=1.58..329,784.01 rows=2,864 width=366) (actual time=0.152..5,017.339 rows=25 loops=1)

4. 0.043 1.011 ↓ 1.8 7 1

Nested Loop Left Join (cost=1.58..32.47 rows=4 width=901) (actual time=0.151..1.011 rows=7 loops=1)

5. 0.019 0.751 ↓ 1.8 7 1

Nested Loop Left Join (cost=1.15..30.35 rows=4 width=379) (actual time=0.106..0.751 rows=7 loops=1)

6. 0.019 0.634 ↓ 1.8 7 1

Nested Loop (cost=0.72..28.18 rows=4 width=326) (actual time=0.080..0.634 rows=7 loops=1)

7. 0.076 0.076 ↑ 1.1 7 1

Index Scan using index_tiktok_sound_artists_on_artist_id_and_tiktok_sound_id on tiktok_sound_artists sound_artists (cost=0.28..8.42 rows=8 width=12) (actual time=0.037..0.076 rows=7 loops=1)

  • Index Cond: (artist_id = 292,846)
  • Filter: active
  • Rows Removed by Filter: 1
8. 0.539 0.539 ↑ 1.0 1 7

Index Scan using tiktok_sounds_pkey on tiktok_sounds sounds (cost=0.44..2.46 rows=1 width=322) (actual time=0.027..0.077 rows=1 loops=7)

  • Index Cond: (id = sound_artists.tiktok_sound_id)
  • Filter: ((last_refreshed_at > '2020-08-28 03:54:28'::timestamp without time zone) OR (post_count >= 10,000))
9. 0.098 0.098 ↓ 0.0 0 7

Index Scan using tiktok_users_pkey on tiktok_users users (cost=0.43..0.53 rows=1 width=61) (actual time=0.005..0.014 rows=0 loops=7)

  • Index Cond: (id = sounds.tiktok_user_id)
10. 0.217 0.217 ↑ 1.0 1 7

Index Scan using index_tiktok_sound_growth_metrics_on_tiktok_sound_id on tiktok_sound_growth_metrics metrics (cost=0.43..0.51 rows=1 width=417) (actual time=0.031..0.031 rows=1 loops=7)

  • Index Cond: (tiktok_sound_id = sounds.id)
  • Filter: (updated_at >= '2020-08-25 03:55:38.298846'::timestamp without time zone)
11. 0.041 5.133 ↓ 1.6 8 1

Nested Loop Left Join (cost=1.87..17,774.37 rows=5 width=917) (actual time=0.857..5.133 rows=8 loops=1)

12. 0.016 4.948 ↓ 1.6 8 1

Nested Loop Left Join (cost=1.43..17,771.72 rows=5 width=487) (actual time=0.842..4.948 rows=8 loops=1)

13. 0.092 4.716 ↓ 1.6 8 1

Nested Loop (cost=1.00..17,769.01 rows=5 width=434) (actual time=0.839..4.716 rows=8 loops=1)

14. 0.594 0.594 ↑ 2.5 62 1

Index Scan using index_it_songs_on_it_account_id on it_songs (cost=0.43..151.60 rows=153 width=116) (actual time=0.020..0.594 rows=62 loops=1)

  • Index Cond: (it_account_id = 236,596)
15. 4.030 4.030 ↓ 0.0 0 62

Index Scan using index_tiktok_sounds_on_it_song_id on tiktok_sounds sounds_1 (cost=0.56..114.59 rows=56 width=330) (actual time=0.062..0.065 rows=0 loops=62)

  • Index Cond: (it_song_id = it_songs.id)
  • Filter: ((last_refreshed_at > '2020-08-28 03:54:28'::timestamp without time zone) OR (post_count >= 10,000))
  • Rows Removed by Filter: 0
16. 0.216 0.216 ↓ 0.0 0 8

Index Scan using tiktok_users_pkey on tiktok_users users_1 (cost=0.43..0.53 rows=1 width=61) (actual time=0.004..0.027 rows=0 loops=8)

  • Index Cond: (id = sounds_1.tiktok_user_id)
17. 0.144 0.144 ↑ 1.0 1 8

Index Scan using index_tiktok_sound_growth_metrics_on_tiktok_sound_id on tiktok_sound_growth_metrics metrics_1 (cost=0.43..0.51 rows=1 width=417) (actual time=0.017..0.018 rows=1 loops=8)

  • Index Cond: (tiktok_sound_id = sounds_1.id)
  • Filter: (updated_at >= '2020-08-25 03:55:38.298846'::timestamp without time zone)
  • Rows Removed by Filter: 0
18. 0.021 5,011.168 ↑ 285.5 10 1

Subquery Scan on *SELECT* 3 (cost=260,156.81..311,977.08 rows=2,855 width=870) (actual time=2,839.892..5,011.168 rows=10 loops=1)

19. 0.116 5,011.147 ↑ 285.5 10 1

Nested Loop Left Join (cost=260,156.81..311,948.53 rows=2,855 width=870) (actual time=2,839.889..5,011.147 rows=10 loops=1)

20. 0.047 5,010.881 ↑ 285.5 10 1

Nested Loop Left Join (cost=260,156.38..310,427.66 rows=2,855 width=431) (actual time=2,839.848..5,010.881 rows=10 loops=1)

21. 0.020 5,010.794 ↑ 285.5 10 1

Nested Loop (cost=260,155.94..308,878.88 rows=2,855 width=378) (actual time=2,839.840..5,010.794 rows=10 loops=1)

22. 1.104 1.104 ↑ 1.0 1 1

Index Scan using sp_accounts_pkey on sp_accounts (cost=0.44..2.46 rows=1 width=17) (actual time=0.115..1.104 rows=1 loops=1)

  • Index Cond: (id = 1,106,077)
23. 82.649 5,009.670 ↑ 285.5 10 1

Merge Join (cost=260,155.50..308,847.88 rows=2,855 width=373) (actual time=2,839.720..5,009.670 rows=10 loops=1)

  • Merge Cond: (sounds_2.sp_track_id = sp_tracks.id)
24. 4,905.495 4,905.495 ↑ 35.1 246,055 1

Index Scan using index_tiktok_sounds_on_sp_track_id on tiktok_sounds sounds_2 (cost=0.56..1,390,682.54 rows=8,642,726 width=330) (actual time=0.021..4,905.495 rows=246,055 loops=1)

  • Filter: ((last_refreshed_at > '2020-08-28 03:54:28'::timestamp without time zone) OR (post_count >= 10,000))
  • Rows Removed by Filter: 172,585
25. 0.103 21.526 ↑ 2,626.6 91 1

Materialize (cost=260,154.89..261,349.98 rows=239,018 width=55) (actual time=21.350..21.526 rows=91 loops=1)

26. 0.191 21.423 ↑ 2,685.6 89 1

Sort (cost=260,154.89..260,752.44 rows=239,018 width=55) (actual time=21.345..21.423 rows=89 loops=1)

  • Sort Key: sp_tracks.id
  • Sort Method: quicksort Memory: 34kB
27. 21.232 21.232 ↑ 2,685.6 89 1

Index Scan using index_sp_tracks_on_primary_account_id_sanitized_isrc_name_popul on sp_tracks (cost=0.69..234,132.51 rows=239,018 width=55) (actual time=0.054..21.232 rows=89 loops=1)

  • Index Cond: (primary_sp_account_id = 1,106,077)
28. 0.040 0.040 ↓ 0.0 0 10

Index Scan using tiktok_users_pkey on tiktok_users users_2 (cost=0.43..0.53 rows=1 width=61) (actual time=0.002..0.004 rows=0 loops=10)

  • Index Cond: (id = sounds_2.tiktok_user_id)
29. 0.150 0.150 ↑ 1.0 1 10

Index Scan using index_tiktok_sound_growth_metrics_on_tiktok_sound_id on tiktok_sound_growth_metrics metrics_2 (cost=0.43..0.51 rows=1 width=417) (actual time=0.014..0.015 rows=1 loops=10)

  • Index Cond: (tiktok_sound_id = sounds_2.id)
  • Filter: (updated_at >= '2020-08-25 03:55:38.298846'::timestamp without time zone)
  • Rows Removed by Filter: 0
Planning time : 3.430 ms
Execution time : 5,018.183 ms