Poor performance for delete query

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

Responses

Browse pgsql-performance by date

  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