Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why do you create that intermediate table in the first place? Did you try to delete the rows from taskflag directly? (without the intermediate table). –  a_horse_with_no_name Jun 5 '12 at 6:50
    
I have four other tables to also trim, and I (ahem) assumed that it had to be worthwhile to factor out the selection of the ids to keep. –  bmargulies Jun 5 '12 at 10:24
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
I kept trying to remember how to write this as a join and could not think of not exists. Thanks! –  bmargulies Jun 5 '12 at 10:25
1  
+1 for the ANALYZE –  dezso Jun 5 '12 at 10:38
add comment

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.

share|improve this answer
    
A third solution might be to create a new table with just the rows to keep (CREATE TABLE .. AS SELECT ...) then drop the old one and rename the new one. –  a_horse_with_no_name Jun 5 '12 at 9:15
    
@a_horse_with_no_name - added this case as well, thanks for the suggestion. –  dezso Jun 5 '12 at 9:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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