How to speed up delete where not in - Mailing list pgsql-general

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.