speeding up a deletion in postgresql

Question

I came up with:

drop table if exists idtemp;
create temp table idtemp as 
      select documentid from taskflag where taskid='coref' and state = 2 
         order by statechanged asc limit howmany;
create unique index on idtemp(documentid);

-- trim taskflag to the first N docs ordered by coref.
delete from taskflag where documentid not in (select documentid from idtemp) ;

This is very slow when there are 120k records in taskflag and I'm keeping 10k.

Taskflag looks like:

\d taskflag
                Table "public.taskflag"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 documentid   | character varying(64)       | not null
 taskid       | character varying(64)       | not null
 state        | smallint                    | 
 statechanged | timestamp without time zone | 
Indexes:
    "taskflag_pkey" PRIMARY KEY, btree (documentid, taskid)
    "task_index2" btree (documentid)
    "task_index4" btree (taskid, state, statechanged)

Explain says:

                                QUERY PLAN                                    
----------------------------------------------------------------------------------
 Delete on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
   ->  Seq Scan on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..449.00 rows=10000 width=146)
                 ->  Seq Scan on idtemp  (cost=0.00..184.00 rows=10000 width=146)
(6 rows)

Should I just arrange for the temp table to contain the ones I am keeping?

Answer 1

The easiest optimization would probably be to let the planner use a hash anti join, by rewriting the query as:

 delete from taskflag where not exists
  (select 1 from idtemp where documentid=taskflag.documentid);

also you may need to ANALYZE the temp table immediately after populating it.

Answer 2

I've set up a test. Trying to delete the way you did gives pretty much the same query plan. If you perform an ANALYZE idtemp before deleting, the plan changes to the following:

                                QUERY PLAN
---------------------------------------------------------------------------
 Delete on taskflag  (cost=198.00..3041.00 rows=60000 width=6)
   ->  Seq Scan on taskflag  (cost=198.00..3041.00 rows=60000 width=6)
         Filter: (NOT (hashed SubPlan 1))
         SubPlan 1
           ->  Seq Scan on idtemp  (cost=0.00..173.00 rows=10000 width=24)

And the way a_horse_with_no_name suggested:

EXPLAIN delete from taskflag where not (taskid='coref' and state = 2);

                             QUERY PLAN
---------------------------------------------------------------------
 Delete on taskflag  (cost=0.00..3509.13 rows=1 width=6)
   ->  Seq Scan on taskflag  (cost=0.00..3509.13 rows=1 width=6)
         Filter: (((taskid)::text <> 'coref'::text) OR (state <> 2))

On my test box, I had no patience to wait for your version to complete :) The first version with ANALYZE completes a bit over 1 second (but it takes over an additional half second performing the INSERT and the CREATE INDEX, which means around 2 secs overall), the rephrased DELETE just a bit under 1 second.

@a_horse_with_no_name's second suggestion (see the comment below) with the following code

DROP TABLE IF EXISTS idtemp;

CREATE TABLE idtemp AS 
    SELECT documentid 
    FROM taskflag 
    WHERE taskid = 'coref' AND state = 2 
    ORDER BY statechanged ASC LIMIT 10000;

ALTER TABLE idtemp
ADD CONSTRAINT pk_taskflag PRIMARY KEY (documentid, taskid);

CREATE INDEX idx_11 ON idtemp (documentid);

CREATE INDEX idx_12 ON idtemp (taskid, state, statechanged);

ANALYZE idtemp;

DROP TABLE taskflag;

ALTER TABLE idtemp RENAME TO taskflag;

also takes some 900 ms, which means that in this particular case (with my particular test data) this approach is quite competitive. If your taskflag table had dependent objects, it would not work though.