gistfile1.txt

EXPLAIN ANALYZE
    WITH exclude_tracks AS (
        SELECT recording_mbid
          FROM mapping.tracks_of_the_year t
         WHERE user_name = 'ribombee'
    ), tracks AS (
        SELECT recording_mbid, listen_count
          FROM mapping.tracks_of_the_year t
         WHERE user_name IN ('kazatchok', 'tn5421', 'Shwedish')
           AND recording_mbid NOT IN (SELECT * FROM exclude_tracks)
    ) SELECT q.recording_mbid
           , r.name AS recording_name
           , ac.name AS artist_credit_name
           , sum(listen_count) AS listen_count
       FROM tracks AS q
       JOIN recording r
         ON q.recording_mbid = r.gid
       JOIN artist_credit ac
         ON r.artist_credit = ac.id
   GROUP BY q.recording_mbid, r.name, ac.name
   ORDER BY listen_count DESC;                                                                                             
                                                                                             
                                                                                             QUERY PLAN

--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
 Sort  (cost=198790.89..198874.65 rows=33504 width=70) (actual time=63869.190..63941.463 rows=63597 loops=1)
   Sort Key: (sum(t.listen_count)) DESC
   Sort Method: external merge  Disk: 4296kB
   ->  Finalize GroupAggregate  (cost=192121.72..196272.72 rows=33504 width=70) (actual time=63763.557..63907.481 rows=63597 loops=1)
         Group Key: t.recording_mbid, r.name, ac.name
         ->  Gather Merge  (cost=192121.72..195658.48 rows=27920 width=70) (actual time=63763.512..63864.700 rows=63650 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Partial GroupAggregate  (cost=191121.70..191435.80 rows=13960 width=70) (actual time=63699.982..63720.099 rows=21217 loops=3)
                     Group Key: t.recording_mbid, r.name, ac.name
                     ->  Sort  (cost=191121.70..191156.60 rows=13960 width=66) (actual time=63699.937..63702.300 rows=21658 loops=3)
                           Sort Key: t.recording_mbid, r.name, ac.name
                           Sort Method: quicksort  Memory: 3194kB
                           Worker 0:  Sort Method: quicksort  Memory: 3112kB
                           Worker 1:  Sort Method: quicksort  Memory: 3206kB
                           ->  Nested Loop  (cost=7943.77..190160.62 rows=13960 width=66) (actual time=121.240..63647.342 rows=21658 loops=3)
                                 ->  Nested Loop  (cost=7943.34..183844.88 rows=13960 width=44) (actual time=121.144..58703.316 rows=21658 loops=3)
                                       ->  Parallel Bitmap Heap Scan on tracks_of_the_year t  (cost=7942.77..74831.41 rows=13960 width=20) (actual time=91.189..275.110 rows=21821 loops=3)
                                             Recheck Cond: (user_name = ANY ('{kazatchok,tn5421,Shwedish}'::text[]))
                                             Filter: (NOT (hashed SubPlan 1))
                                             Rows Removed by Filter: 388
                                             Heap Blocks: exact=14098
                                             ->  Bitmap Index Scan on tracks_of_the_year_ndx_user_name(cost=0.00..1440.24 rows=67008 width=0) (actual time=78.467..78.467 rows=66626 loops=1)
                                                   Index Cond: (user_name = ANY ('{kazatchok,tn5421,Shwedish}'::text[]))
                                             SubPlan 1
                                               ->  Bitmap Heap Scan on tracks_of_the_year t_1  (cost=43.02..6489.49 rows=1866 width=16) (actual time=21.200..45.227 rows=7226 loops=3)
                                                     Recheck Cond: (user_name = 'ribombee'::text)
                                                     Heap Blocks: exact=6998
                                                     ->  Bitmap Index Scan on tracks_of_the_year_ndx_user_name  (cost=0.00..42.55 rows=1866 width=0) (actual time=17.525..17.525 rows=7226 loops=3)
                                                           Index Cond: (user_name = 'ribombee'::text)
                                       ->  Index Scan using recording_idx_gid on recording r  (cost=0.56..7.81 rows=1 width=40) (actual time=2.674..2.674 rows=1 loops=65462)
                                             Index Cond: (gid = t.recording_mbid)
                                 ->  Index Scan using artist_credit_pkey on artist_credit ac  (cost=0.43..0.45 rows=1 width=30) (actual time=0.225..0.225 rows=1 loops=64975)
                                       Index Cond: (id = r.artist_credit)
 Planning Time: 2.014 ms
 JIT:
   Functions: 114
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 19.949 ms, Inlining 0.000 ms, Optimization 2.341 ms, Emission 47.116 ms, Total 69.
406 ms
 Execution Time: 63958.129 ms
(40 rows)