HashAggregate.1.sql

HashAggregate.1.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
-------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=33227.59..58222.98 rows=294587 width=170)
   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)
   ->  HashAggregate  (cost=521.73..523.73 rows=200 width=8)
         Group Key: subscribers.user_id
         ->  CTE Scan on subscribers  (cost=0.00..463.76 rows=23188 width=8)
   ->  Index Scan using devices_user_id_idx on devices  (cost=0.43..124.84 rows=13 width=170)
         Index Cond: (user_id = subscribers.user_id)
(11 rows)