HashAggregate.3.sql

HashAggregate.3.sql

postgres=# SET enable_hashjoin=off;
SET
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..1384775.03 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..721.73 rows=20000 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..67.44 rows=13 width=170)
         Index Cond: (user_id = subscribers.user_id)
(11 rows)