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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
