Skip site navigation (1) Skip section navigation (2)

Speeding up 'bulk' delete (and/or seeing what is going on whilethe delete is being processed)

From: Steve T <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Speeding up 'bulk' delete (and/or seeing what is going on whilethe delete is being processed)
Date: 2009-08-27 15:27:18
Message-ID: 1251386838.3634.357.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-novice
Postgresql 8.1

I have a large'ish table containing supplier products. One of the
suppliers is no longer being used, and I want to delete all the products
for that supplier that haven't as yet been used.
To do this I have created a smaller table of unused products and then
delete from the main table where the product is in the sub table - ie:

create temp table _sub_recs as 
    select distinct sprod.recNo 
        from supplierProduct as Sprod 
                 left outer join sourcedetupgr as srcu 
                 on sprod.recno = srcu.supplierproductrecno 
        where sprod.supplierrecno = 1 
        and srcu.recno is null;

create unique index _sub_recs1 on _sub_recs (recno);

vacuum analyze verbose _sub_recs;

explain  delete from supplierProduct 
                where supplierrecno = 1 
                and recNo in 
                   (select recNo from _sub_recs);

Which gives:
                                  QUERY
PLAN                                   
-------------------------------------------------------------------------------
 Hash IN Join  (cost=7594.88..42240.95 rows=260517 width=6)
   Hash Cond: ("outer".recno = "inner".recno)
   ->  Seq Scan on supplierproduct  (cost=0.00..19062.44 rows=423684
width=10)
         Filter: (supplierrecno = 1)
   ->  Hash  (cost=5256.70..5256.70 rows=364870 width=4)
         ->  Seq Scan on _sub_recs  (cost=0.00..5256.70 rows=364870
width=4)
(6 rows)


The problem is that the query, when run in earnest, disappears off into
the sunset for hours. How can I see how it is progressing (is the only
way setting up a procedure and doing explicit transactions for each
single delete)? I also tried 'delete .... using' - but that gave the
same plan. 

Is is better to use the IN format above or EXISTS?
    
All the above is being run directly in psql.


Responses

pgsql-novice by date

Next:From: Alan HodgsonDate: 2009-08-27 16:34:33
Subject: Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)
Previous:From: Greg StarkDate: 2009-08-27 15:17:04
Subject: Re: Simple like filter

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group