Sequential scan with large sub-query filter "never" ends I'm using postgres 9.2.4, and run the following query:

explain analyze select * from bubu where id not in
     (select bubu_id from kuku limit 33554431);
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on bubu  (cost=913175.15..1041265.77 rows=1761465 width=160) (actual time=37565.575..42569.266 rows=596 loops=1)
   Filter: (NOT (hashed SubPlan 1))
   Rows Removed by Filter: 3511745
   SubPlan 1
     -&gt;  Limit  (cost=0.00..829289.07 rows=33554431 width=8) (actual time=20.528..22363.943 rows=33554431 loops=1)
           -&gt;  Seq Scan on kuku  (cost=0.00..830246.84 rows=33593184 width=8) (actual time=20.528..18741.263 rows=33554431 loops=1)
 Total runtime: 42579.485 ms
(7 rows)


And then the second query, which never ends:

explain analyze select * from bubu where id not in
   (select bubu_id from kuku limit 33554433)


The query without limit also gets stuck

explain analyze select * from bubu where id not in
   (select bubu_id from kuku) 


Explain of the stuck query:

explain select * from bubu where id not in
   (select bubu_id from kuku) 

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Seq Scan on bubu  (cost=0.00..2137396495180.43 rows=1761465 width=160)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     -&gt;  Materialize  (cost=0.00..1129436.76 rows=33593184 width=8)
           -&gt;  Seq Scan on kuku  (cost=0.00..830246.84 rows=33593184 width=8)
   (5 rows)


The query I actually need is

delete from bubu where id not in
     (select bubu_id from kuku)


Is there any PostgreSQL parameter I can tune to avoid this issue?
Your queries are slow because they in fact do a lot. In your

explain analyze
    select * from bubu where id not in
    (select bubu_id from kuku limit 33554431);


example, the database is fetching up to 33,554,431 rows in undefined order from kuku, doing another scan on bubu to select rows that are not among the fetched 33m+ rows, and then finally fetching and returning rows that fit such a condition.

If you wanna do

delete from bubu where id not in
(select bubu_id from kuku)


then your options are:


Just run it and wait.
Do it in chunks:


Fetch all the IDs that you are going to compare against into a separate table:

CREATE TABLE temp_bubu_ids AS SELECT bubu_id FROM kuku;

Fetch 1000 or so IDs from the IDs table, delete them and then delete rows from bubu that match your condition:

WITH deleted_ids AS (
    DELETE FROM temp_bubu_ids

    -- DELETE doesn't support LIMIT
    WHERE ctid IN (
        SELECT ctid
        FROM temp_bubu_ids
        LIMIT 1000
    )
    RETURNING id
)
DELETE FROM bubu
WHERE id NOT IN (
    SELECT id
    FROM deleted_ids
)

Rinse and repeat until all the appropriate rows get deleted.