From: | Jonatan Evald Buus <jonatan(dot)buus(at)cellpointmobile(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Poor performance for delete query |
Date: | 2014-04-24 19:42:41 |
Message-ID: | CABnF-8Lyps8ribpp=BOFnqGEeWp2K5iuLukMo3txQ5UkgHjF7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Konoplev | 2014-04-24 19:57:50 | Re: tsearch2, large data and indexes |
Previous Message | Sergey Konoplev | 2014-04-24 19:27:06 | Re: tsearch2, large data and indexes |