explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pJDf

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 1,908.285 ↑ 114.5 25 1

Sort (cost=330,013.79..330,020.95 rows=2,863 width=366) (actual time=1,908.279..1,908.285 rows=25 loops=1)

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

HashAggregate (cost=329,820.78..329,849.41 rows=2,863 width=366) (actual time=1,908.185..1,908.211 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.031 1,907.949 ↑ 114.5 25 1

Append (cost=1.58..329,677.63 rows=2,863 width=366) (actual time=4.265..1,907.949 rows=25 loops=1)

4. 0.051 36.153 ↓ 1.8 7 1

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

5. 0.018 10.783 ↓ 1.8 7 1

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

6. 0.021 6.341 ↓ 1.8 7 1

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

7. 1.945 1.945 ↑ 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=1.075..1.945 rows=7 loops=1)

  • Index Cond: (artist_id = 292,846)
  • Filter: active
  • Rows Removed by Filter: 1
8. 4.375 4.375 ↑ 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.383..0.625 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. 4.424 4.424 ↓ 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.057..0.632 rows=0 loops=7)

  • Index Cond: (id = sounds.tiktok_user_id)
10. 25.319 25.319 ↑ 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=3.616..3.617 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.064 69.542 ↓ 1.6 8 1

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

12. 0.017 56.590 ↓ 1.6 8 1

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

13. 0.192 48.853 ↓ 1.6 8 1

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

14. 34.339 34.339 ↑ 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.586..34.339 rows=62 loops=1)

  • Index Cond: (it_account_id = 236,596)
15. 14.322 14.322 ↓ 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.230..0.231 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. 7.720 7.720 ↓ 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.090..0.965 rows=0 loops=8)

  • Index Cond: (id = sounds_1.tiktok_user_id)
17. 12.888 12.888 ↑ 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=1.611..1.611 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.022 1,802.223 ↑ 285.4 10 1

Subquery Scan on *SELECT* 3 (cost=260,066.23..311,870.70 rows=2,854 width=870) (actual time=856.648..1,802.223 rows=10 loops=1)

19. 0.122 1,802.201 ↑ 285.4 10 1

Nested Loop Left Join (cost=260,066.23..311,842.16 rows=2,854 width=870) (actual time=856.645..1,802.201 rows=10 loops=1)

20. 0.084 1,797.259 ↑ 285.4 10 1

Nested Loop Left Join (cost=260,065.80..310,321.77 rows=2,854 width=431) (actual time=856.581..1,797.259 rows=10 loops=1)

21. 0.034 1,797.135 ↑ 285.4 10 1

Nested Loop (cost=260,065.36..308,773.59 rows=2,854 width=378) (actual time=856.527..1,797.135 rows=10 loops=1)

22. 1.462 1.462 ↑ 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.030..1.462 rows=1 loops=1)

  • Index Cond: (id = 1,106,077)
23. 75.419 1,795.639 ↑ 285.4 10 1

Merge Join (cost=260,064.92..308,742.59 rows=2,854 width=373) (actual time=856.475..1,795.639 rows=10 loops=1)

  • Merge Cond: (sounds_2.sp_track_id = sp_tracks.id)
24. 1,574.264 1,574.264 ↑ 35.1 245,866 1

Index Scan using index_tiktok_sounds_on_sp_track_id on tiktok_sounds sounds_2 (cost=0.56..1,390,269.66 rows=8,639,887 width=330) (actual time=0.020..1,574.264 rows=245,866 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,735
25. 0.088 145.956 ↑ 2,625.6 91 1

Materialize (cost=260,064.31..261,258.98 rows=238,934 width=55) (actual time=145.798..145.956 rows=91 loops=1)

26. 0.222 145.868 ↑ 2,684.7 89 1

Sort (cost=260,064.31..260,661.65 rows=238,934 width=55) (actual time=145.793..145.868 rows=89 loops=1)

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

Index Scan using index_sp_tracks_on_primary_account_id_sanitized_isrc_name_popul on sp_tracks (cost=0.69..234,052.04 rows=238,934 width=55) (actual time=1.484..145.646 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. 4.820 4.820 ↑ 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.482..0.482 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 : 5.000 ms
Execution time : 1,908.772 ms