Speeding up 'bulk' delete (and/or seeing what is going on while the 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 while the delete is being processed)
Date: 2009-08-27 15:27:18
Message-ID: 1251386838.3634.357.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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