Search Postgresql Archives

How to speed up delete where not in

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux