0

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
     ->  Limit  (cost=0.00..829289.07 rows=33554431 width=8) (actual time=20.528..22363.943 rows=33554431 loops=1)
           ->  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
     ->  Materialize  (cost=0.00..1129436.76 rows=33593184 width=8)
           ->  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?

  • Have you tried rewriting the query to use NOT EXISTS instead of NOT IN? Is there an index on kuku (bubu_id)? – sticky bit Aug 5 '18 at 18:17
  • @stickybit , there is an index. NOT EXISTS indeed helps in this case! – Evgeny Aug 5 '18 at 19:26
2

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:

  1. Just run it and wait.
  2. Do it in chunks:

    1. 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;
      
    2. 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
      )
      
    3. Rinse and repeat until all the appropriate rows get deleted.

  • Another option is to create new_table as select bubu_id from kuku limit 33554431, then index bubu_id on new_table, then put new_table in your subquery. – Victor Di Leo Aug 6 '18 at 1:24
0

Not sure if it qualifies as an answer...

My question was asked here (failed to come up with the "Materialized" keyword) and got the answer I looked for: https://stackoverflow.com/questions/26477353/postgres-materialize-causes-poor-performance-in-delete-query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.