QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=394382.34..394395.36 rows=274 width=981) (actual time=20475.500..20484.645 rows=500 loops=1) CTE playlists -> GroupAggregate (cost=11436.24..12751.96 rows=54819 width=46) (actual time=79.565..128.089 rows=16966 loops=1) Group Key: spy_playlist_track.track_spyid -> Sort (cost=11436.24..11646.40 rows=84064 width=46) (actual time=79.526..86.888 rows=26017 loops=1) Sort Key: spy_playlist_track.track_spyid Sort Method: quicksort Memory: 2801kB -> Seq Scan on spy_playlist_track (cost=0.00..1973.64 rows=84064 width=46) (actual time=0.015..16.764 rows=26017 loops=1) CTE adjustment_factor -> GroupAggregate (cost=320208.90..354088.79 rows=104636 width=27) (actual time=15960.575..19269.442 rows=16986 loops=1) Group Key: spy_tracks_1.spyid -> Sort (cost=320208.90..323857.07 rows=1459268 width=27) (actual time=15960.305..18515.463 rows=1569919 loops=1) Sort Key: spy_tracks_1.spyid Sort Method: external merge Disk: 58280kB -> Merge Join (cost=30343.82..135887.11 rows=1459268 width=27) (actual time=162.698..9648.160 rows=1569919 loops=1) Merge Cond: ((spy_artist_metrics.artist_spyid)::text = (spy_tracks_1.primary_artist_spyid)::text) -> Index Scan using spy_artist_metrics_pkey on spy_artist_metrics (cost=0.42..80875.50 rows=1007040 width=27) (actual time=0.011..7994.782 rows=1006993 loops=1) -> Materialize (cost=30343.40..30866.58 rows=104636 width=46) (actual time=162.679..480.199 rows=1569897 loops=1) -> Sort (cost=30343.40..30604.99 rows=104636 width=46) (actual time=162.676..169.568 rows=16993 loops=1) Sort Key: spy_tracks_1.primary_artist_spyid Sort Method: quicksort Memory: 2096kB -> Seq Scan on spy_tracks spy_tracks_1 (cost=0.00..18399.36 rows=104636 width=46) (actual time=3.500..123.693 rows=16993 loops=1) CTE all_popularity -> GroupAggregate (cost=26017.81..26205.62 rows=1 width=43) (actual time=20147.220..20229.295 rows=2497 loops=1) Group Key: spy_track_popularity.track_spyid -> Sort (cost=26017.81..26018.40 rows=234 width=43) (actual time=20147.117..20151.783 rows=14776 loops=1) Sort Key: spy_track_popularity.track_spyid Sort Method: quicksort Memory: 1539kB -> Hash Join (cost=4218.67..26008.60 rows=234 width=43) (actual time=20021.632..20120.148 rows=14776 loops=1) Hash Cond: ((spy_track_popularity.track_spyid)::text = (adjustment_factor.track_spyid)::text) -> Seq Scan on spy_track_popularity (cost=0.00..20588.20 rows=29 width=35) (actual time=727.879..784.614 rows=14784 loops=1) Filter: (as_of > (('now'::cstring)::date - '7 days'::interval)) Rows Removed by Filter: 711755 -> Hash (cost=2092.72..2092.72 rows=104636 width=40) (actual time=19292.143..19292.143 rows=16986 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 779kB -> CTE Scan on adjustment_factor (cost=0.00..2092.72 rows=104636 width=40) (actual time=15960.579..19280.651 rows=16986 loops=1) -> WindowAgg (cost=1335.98..1348.99 rows=274 width=981) (actual time=20475.498..20484.465 rows=500 loops=1) -> Sort (cost=1335.98..1336.66 rows=274 width=981) (actual time=20474.368..20474.555 rows=500 loops=1) Sort Key: all_popularity.diff DESC Sort Method: external merge Disk: 3128kB -> Nested Loop Left Join (cost=8.48..1324.88 rows=274 width=981) (actual time=20391.901..20462.364 rows=2493 loops=1) Join Filter: ((user_reactions.track_id)::text = (spy_tracks.spyid)::text) -> Nested Loop Left Join (cost=8.48..1319.01 rows=274 width=975) (actual time=20391.342..20459.867 rows=2493 loops=1) Join Filter: ((user_likes.track_id)::text = (spy_tracks.spyid)::text) -> Hash Join (cost=8.48..1313.17 rows=274 width=967) (actual time=20390.682..20457.122 rows=2493 loops=1) Hash Cond: ((playlists.track_spyid)::text = (all_popularity.track_spyid)::text) -> CTE Scan on playlists (cost=0.00..1096.38 rows=54819 width=64) (actual time=79.569..139.251 rows=16966 loops=1) -> Hash (cost=8.47..8.47 rows=1 width=967) (actual time=20311.078..20311.078 rows=2493 loops=1) Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3133kB -> Nested Loop (cost=0.42..8.47 rows=1 width=967) (actual time=20159.972..20306.127 rows=2493 loops=1) -> CTE Scan on all_popularity (cost=0.00..0.02 rows=1 width=80) (actual time=20147.223..20232.166 rows=2497 loops=1) -> Index Scan using spy_tracks_pkey on spy_tracks (cost=0.42..8.44 rows=1 width=887) (actual time=0.013..0.028 rows=1 loops=2497) Index Cond: ((spyid)::text = (all_popularity.track_spyid)::text) Filter: (NOT (tags ?| '{interested}'::text[])) Rows Removed by Filter: 0 -> Materialize (cost=0.00..1.73 rows=1 width=31) (actual time=0.000..0.000 rows=0 loops=2493) -> Seq Scan on user_likes (cost=0.00..1.73 rows=1 width=31) (actual time=0.656..0.656 rows=0 loops=1) Filter: ((user_id)::text = '1'::text) Rows Removed by Filter: 69 -> Materialize (cost=0.00..1.77 rows=1 width=29) (actual time=0.000..0.000 rows=0 loops=2493) -> Seq Scan on user_reactions (cost=0.00..1.76 rows=1 width=29) (actual time=0.553..0.553 rows=0 loops=1) Filter: ((user_id)::text = '1'::text) Rows Removed by Filter: 87 Planning time: 62.344 ms Execution time: 20498.208 ms (65 rows)