HashAggregate.2.sql

HashAggregate.2.sql

postgres=# 
EXPLAIN WITH subscribers AS MATERIALIZED (SELECT user_id FROM playlist_subscriptions WHERE list_id = 3343594)
SELECT * FROM devices WHERE user_id IN (SELECT user_id FROM subscribers);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=33677.16..245826.56 rows=294587 width=170)
   Hash Cond: (devices.user_id = subscribers.user_id)
   CTE subscribers
     ->  Bitmap Heap Scan on playlist_subscriptions  (cost=1350.13..32705.43 rows=23188 width=8)
           Recheck Cond: (list_id = 3343594)
           ->  Bitmap Index Scan on playlist_subscriptions_list_id_user_id_uniq  (cost=0.00..1344.34 rows=23188 width=0)
                 Index Cond: (list_id = 3343594)
   ->  Seq Scan on devices  (cost=0.00..194390.62 rows=5516762 width=170)
   ->  Hash  (cost=721.73..721.73 rows=20000 width=8)
         ->  HashAggregate  (cost=521.73..721.73 rows=20000 width=8)
               Group Key: subscribers.user_id
               ->  CTE Scan on subscribers  (cost=0.00..463.76 rows=23188 width=8)
(12 rows)