0

I am attempting to optimize the following query, and have gotten to a stage where I am pretty stumped on what I could do to improve. Given a spotify database, essentially what I am trying to do is "Given an input song "spotify:track:someuuid", find all playlists which contain the song, then for every unique song that exists in the playlist, calculate how frequently that song occurs in the same playlist divided by total number of playlists which contain the input song"

SELECT t1.*, t2.score                                                                                                                                                                                                                                         
FROM tracks t1                                                                                                                                                                                                                                                
JOIN (                                                                                                                                                                                                                                                        
    SELECT track_uri, COUNT(junc.pid)*1.00 / pid_count AS score                                                                                                                                                                                               
    FROM playlist_tracks AS junc                                                                                                                                                                                                                              
    JOIN (                                                                                                                                                                                                                                                    
        SELECT junc_temp.pid, COUNT(pid) over () AS pid_count                                                                                                                                                                                                 
        FROM playlist_tracks AS junc_temp                                                                                                                                                                                                                     
        WHERE track_uri={input}                                                                                                                                                                                                                                    
    ) temp_junc2 ON temp_junc2.pid = junc.pid AND junc.track_uri<>{input}                                                                                                                                                                                          
    GROUP BY junc.track_uri, temp_junc2.pid_count                                                                                                                                                                                                             
) t2 on t2.track_uri=t1.track_uri                                                                                                                                                                                                                             
ORDER BY t2.score DESC                                                                                                                                                                                                                                        
LIMIT 10;

I initially sped things up by adding a compound index to the playlist_tracks table, but beyond that I feel like at this point I need to focus on the query itself. Here is more info on each table

                   Table "public.playlist_tracks"
  Column   |          Type          | Collation | Nullable | Default 
-----------+------------------------+-----------+----------+---------
 pid       | integer                |           | not null | 
 track_uri | character varying(500) |           | not null | 
Indexes:
    "member_index" btree (pid, track_uri)
    "pid_index" btree (pid)
    "track_uri_index" btree (track_uri)
Foreign-key constraints:
    "playlist_tracks_pid_fkey" FOREIGN KEY (pid) REFERENCES playlists(pid) ON UPDATE CASCADE ON DELETE CASCADE
    "playlist_tracks_track_uri_fkey" FOREIGN KEY (track_uri) REFERENCES tracks(track_uri) ON UPDATE CASCADE ON DELETE CASCADE
                           Table "public.tracks"
      Column      |          Type          | Collation | Nullable | Default                                                        
------------------+------------------------+-----------+----------+---------                                                       
 track_uri        | character varying(500) |           | not null |                                                                
 artist_name      | character varying(500) |           |          |                                                                
 track_name       | character varying(500) |           |          |                                                                
 album_name       | character varying(500) |           |          |                                                                
 popularity       | numeric                |           |          |                                                                
 danceability     | numeric                |           |          |                                                                
 energy           | numeric                |           |          |                                                                
 key              | numeric                |           |          |                                                                
 loudness         | numeric                |           |          |                                                                
 mode             | numeric                |           |          |                                                                
 speechiness      | numeric                |           |          |                                                                
 acousticness     | numeric                |           |          |                                                                
 instrumentalness | numeric                |           |          |                                                                
 liveness         | numeric                |           |          |                                                                
 valence          | numeric                |           |          |                                                                
 tempo            | numeric                |           |          |                                                                
 duration_ms      | numeric                |           |          |                                                                
 time_signature   | numeric                |           |          |                                                                
Indexes:                                                         
    "tracks_pkey" PRIMARY KEY, btree (track_uri)
Referenced by:                                                   
    TABLE "playlist_tracks" CONSTRAINT "playlist_tracks_track_uri_fkey" FOREIGN KEY (track_uri) REFERENCES tracks(track_uri) ON UPDATE CASCADE ON DELETE CASCADE

Explain Analyze of the above query for some arbitrary song:

                                                                                   QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=13410652.57..13430207.07 rows=7821800 width=189) (actual time=18253.088..18361.218 rows=146217 loops=1)
   Sort Key: ((((count(junc.pid))::numeric * 1.00) / ((count(junc_temp.pid) OVER (?)))::numeric)) DESC
   Sort Method: external merge  Disk: 26272kB
   ->  Merge Join  (cost=9305824.43..10349583.37 rows=7821800 width=189) (actual time=9217.916..18003.179 rows=146217 loops=1)
         Merge Cond: ((junc.track_uri)::text = (t1.track_uri)::text)
         ->  GroupAggregate  (cost=9305823.88..9785035.98 rows=7821800 width=77) (actual time=9217.664..11086.566 rows=146217 loops=1)
               Group Key: junc.track_uri, (count(junc_temp.pid) OVER (?))
               ->  Sort  (cost=9305823.88..9386517.90 rows=32277610 width=49) (actual time=9217.637..10411.602 rows=2394496 loops=1)
                     Sort Key: junc.track_uri, (count(junc_temp.pid) OVER (?))
                     Sort Method: external merge  Disk: 145296kB
                     ->  Nested Loop  (cost=299.16..1970429.45 rows=32277610 width=49) (actual time=598.066..1584.989 rows=2394496 loops=1)
                           ->  WindowAgg  (cost=298.60..81998.47 rows=24004 width=12) (actual time=598.010..604.742 rows=22980 loops=1)
                                 ->  Bitmap Heap Scan on playlist_tracks junc_temp  (cost=298.60..81698.42 rows=24004 width=4) (actual time=489.590..589.347 rows=22980 loops=1)
                                       Recheck Cond: ((track_uri)::text = 'spotify:track:40YcuQysJ0KlGQTeGUosTC'::text)
                                       Heap Blocks: exact=22781
                                       ->  Bitmap Index Scan on track_uri_index  (cost=0.00..292.59 rows=24004 width=0) (actual time=3.828..3.828 rows=22980 loops=1)
                                             Index Cond: ((track_uri)::text = 'spotify:track:40YcuQysJ0KlGQTeGUosTC'::text)
                           ->  Index Only Scan using member_index on playlist_tracks junc  (cost=0.56..65.27 rows=1339 width=41) (actual time=0.007..0.030 rows=104 loops=22980)
                                 Index Cond: (pid = junc_temp.pid)
                                 Filter: ((track_uri)::text <> 'spotify:track:1E769aJ5htmyP2Md68zAVr'::text)
                                 Rows Removed by Filter: 0
                                 Heap Fetches: 0
         ->  Index Scan using tracks_pkey on tracks t1  (cost=0.56..382899.44 rows=2262983 width=157) (actual time=0.076..6143.753 rows=2262292 loops=1)
 Planning Time: 1.354 ms
 JIT:
   Functions: 22
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 8.818 ms, Inlining 29.443 ms, Optimization 297.284 ms, Emission 155.171 ms, Total 490.716 ms
 Execution Time: 18421.865 ms
(29 rows)

Updates


After incorporating some of jjanes changes, it looks like my bottleneck is now within the HashAggregate. The new query plan is this. I'm unsure if this is just as good as it gets or if I can still try to squeeze out better performance. My goal is to get a response sub 1000ms

 Limit  (cost=5378497.79..5378504.85 rows=10 width=32) (actual time=2354.801..2354.991 rows=10 loops=1)
   ->  Nested Loop  (cost=5378497.79..10900157.94 rows=7821800 width=32) (actual time=2085.606..2085.793 rows=10 loops=1)
         ->  HashAggregate  (cost=5378497.23..6165139.56 rows=7821800 width=77) (actual time=2085.530..2085.549 rows=10 loops=1)
               Group Key: (count(junc_temp.pid) OVER (?)), junc.track_uri
               Planned Partitions: 16  Batches: 1  Memory Usage: 40977kB
               ->  Nested Loop  (cost=299.16..1970341.41 rows=32266564 width=49) (actual time=129.213..1163.371 rows=2371520 loops=1)
                     ->  WindowAgg  (cost=298.60..81998.47 rows=24004 width=12) (actual time=129.136..136.831 rows=22980 loops=1)
                           ->  Bitmap Heap Scan on playlist_tracks junc_temp  (cost=298.60..81698.42 rows=24004 width=4) (actual time=10.067..117.258 rows=22980 loops=1)
                                 Recheck Cond: ((track_uri)::text = 'spotify:track:40YcuQysJ0KlGQTeGUosTC'::text)
                                 Heap Blocks: exact=22781
                                 ->  Bitmap Index Scan on track_uri_index  (cost=0.00..292.59 rows=24004 width=0) (actual time=5.437..5.437 rows=22996 loops=1)
                                       Index Cond: ((track_uri)::text = 'spotify:track:40YcuQysJ0KlGQTeGUosTC'::text)
                     ->  Index Only Scan using member_index on playlist_tracks junc  (cost=0.56..65.28 rows=1338 width=41) (actual time=0.008..0.031 rows=103 loops=22980)
                           Index Cond: (pid = junc_temp.pid)
                           Filter: ((track_uri)::text <> 'spotify:track:40YcuQysJ0KlGQTeGUosTC'::text)
                           Rows Removed by Filter: 1
                           Heap Fetches: 581
         ->  Index Only Scan using tracks_track_uri_artist_name_album_name_popularity_danceabi_idx on tracks t1  (cost=0.56..0.60 rows=1 width=118) (actual time=0.022..0.022 rows=1 loops=10)
               Index Cond: (track_uri = (junc.track_uri)::text)
               Heap Fetches: 0
 Planning Time: 1.462 ms
 JIT:
   Functions: 21
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 7.164 ms, Inlining 29.727 ms, Optimization 156.020 ms, Emission 82.803 ms, Total 275.714 ms
 Execution Time: 2377.403 ms
(26 rows)
4
  • What version of PostgreSQL is this? Can you upgrade?
    – jjanes
    Nov 26 '21 at 19:18
  • 13.2, I'm using the postgres:13.2-alpine docker image. I suppose I could upgrade, are there expected to be significant differences? Nov 26 '21 at 19:22
  • There are two {input} in your query, but in the plan these seem to be different values, not the same value used twice, is that correct?
    – jjanes
    Nov 26 '21 at 20:27
  • No, the input is the same value here Nov 26 '21 at 21:19
1

Since you are already on 13, I would not expect upgrading to 14 to make much difference.

Your query shows a LIMIT 10, but the plan doesn't seem to be for a query with a LIMIT.

I don't understand the big picture here (what your calculation means or why you want to see the result) but just looking at the plan details, I see that much of the time goes to the disk sort. You could try increasing work_mem until that changes to a memory sort, or switches to a HashAgg.

I would also try disabling JIT. I doubt JIT helps enough to make up for the time it takes.

1
  • The big picture is that this is just a method to create a similarity metric as part of a song recommendation system. Your suggestions gave me a ~4x speed improvement (from ~18.5s to ~4.5s) using the same input song - the big difference maker is increasing the work_mem. My goal was to get this below 1s, but I'm not sure how feasible that is. Marking as solved regardless because this still helps a ton Nov 26 '21 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.