If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
takes close to 10 minutes.
Do you run those in a single transaction or do you use one transaction per
DELETE ?
In the latter case, postgres will ensure each transaction is commited to
disk, at each commit. Since this involves waiting for the physical I/O to
happen, it is slow. If you do it 30.000 times, it will be 30.000 times
slow.
Note that you should really do :
DELETE FROM table WHERE id IN (huge list of ids).
or
DELETE FROM table JOIN VALUES (list of ids) ON (...)
Also, check your foreign keys using cascading deletes have indexes in the
referencing tables. Without an index, finding the rows to cascade-delete
will be slow.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
