How to speed up delete where not in

["Andrus" <kobruleht2@xxxxxx>]

Hi!


I'm looking for a way to delete records which do not have child rows on big 
tables where lot of rows needs to be deleted. Both tables have lot of other 
foreign key references.


Document headers are in omdok table:

   create table omdok ( dokumnr serial primary key, ... );

Document rows are in omrid table

   CREATE TABLE omrid
   (
     id serial NOT NULL,
     reanr serial NOT NULL,
     dokumnr integer NOT NULL,
     CONSTRAINT omrid_pkey PRIMARY KEY (id),
     CONSTRAINT omrid_dokumnr_fkey FOREIGN KEY (dokumnr)
         REFERENCES omdok (dokumnr) MATCH SIMPLE
         ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY 
IMMEDIATE,
     ....
   );


I tried

   delete from omdok where dokumnr not in      (select dokumnr from omrid)

Query it is running currently 15 hours and is still running.
postgres.exe is using 50% CPU  all the time (this is 2 core CPU).

   explain delete from omdok where dokumnr not in      (select dokumnr from 
omrid)

returns:

   "Delete  (cost=0.00..21971079433.34 rows=220815 width=6)"
   "  ->  Seq Scan on omdok  (cost=0.00..21971079433.34 rows=220815 
width=6)"
   "        Filter: (NOT (SubPlan 1))"
   "        SubPlan 1"
   "          ->  Materialize  (cost=0.00..94756.92 rows=1897261 width=4)"
   "                ->  Seq Scan on omrid  (cost=0.00..77858.61 
rows=1897261 width=4)"

- How to delete parents without child rows fast?
- Will this command finish or is postgres hanging ?
- Currently it is running 15 hours. How many hours it takes to finish ?
How to speed up this query ?


Using

   PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit

   Windows 2003 x64 server with 4 GB RAM.


Posted also in

http://stackoverflow.com/questions/32794828/how-to-speed-up-deleting-documents-without-rows

Andrus. 



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general