Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed) - Mailing list pgsql-novice

From Steve T
Subject Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)
Date
Msg-id 1251386838.3634.357.camel@localhost.localdomain
Whole thread Raw
Responses Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)
List 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.


pgsql-novice by date:

Previous
From: Greg Stark
Date:
Subject: Re: Simple like filter
Next
From: Alan Hodgson
Date:
Subject: Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)