Greetings,
We're currently having very poor performance for the following delete query.
DELETE FROM TopTable WHERE id IN (xx, yy, zz);
We've observed that it takes around 7 seconds under normal load to for each row that's being from TopTable and several minutes pr deleted row under heavy load.
"id" is the primary key in TopTable, and will trigger deletes of a few associated rows in child tables using foreign keys with ON DELETE CASCADE as outlined below:
TopTable
--- Table 1 (references TopTable): 11.811.200 rows
(rows deleted) --- Table 2 (references TopTable): 5.555.190 rows
(rows deleted)--- Table 3 (references TopTable): 8.227.700 rows (now rows deleted)
--- Table 4 (references table 3): 4.294.140 rows (now rows deleted)
--- Table 5 (references table 3): 4.154.850 rows (now rows deleted)
--- Table 6 (references table 5): 5.185.450 rows (now rows deleted)
--- Table 7 (references table 3): 68.206
rows
(now rows deleted)
--- Table 8 (references table 3): 108
rows
(now rows deleted)--- Table 9 (references TopTable): 2448 rows
(now rows deleted)Indexes have been defined for all columns referenced by the foreign key in each of the tables.
Hardware / Software info
Database: PostgreSQL 9.2.2 64-bit
OS: Red Hat Enterprise Linux Server release 5.5
CPU: 8 core Intel Xeon 2.3GHz
RAM: 16GB
Disk: IBM SAN
How do we track down the cause of the poorly performing delete query?
Many thanks for your advice
/Jona