From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Delete performance
Date: Wed, 01 Jun 2011 02:14:11 -0400
Message-ID: <4DE5D8B3.1070403@2ndQuadrant.com> (raw)
In-Reply-To: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
References: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
On 05/30/2011 08:08 PM, Jarrod Chesney wrote:
> My database uses joined table inheritance and my server version is 9.0
> I have about 120,000 records in the table that everything else inherits from, if i truncate-cascaded this table it happens almost instantly. If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it takes close to 10 minutes.
>
> My foreign keys to the base table are all set with "ON DELETE CASCADE".
You may also want to make them DEFERRABLE and then use "SET CONSTRAINTS
ALL DEFERRABLE" so that the constraint checking all happens at one
time. This will cause more memory to be used, but all the constraint
related work will happen in a batch.
You mentioned inheritance. That can cause some unexpected problems
sometimes. You might want to do:
EXPLAIN DELETE FROM ...
To see how this is executing. EXPLAIN works fine on DELETE statements,
too, and it may highlight something strange about how the deletion is
happening. If you can, use EXPLAIN ANALYZE, but note that this will
actually execute the statement--the deletion will happen, it's not just
a test.
There may be a problem with the query plan for the deletion that's
actually causing the issue here, such as missing the right indexes. If
you have trouble reading it, http://explain.depesz.com/ is a good web
resources to help break down where the time is going.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: Delete performance
In-Reply-To: <4DE5D8B3.1070403@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox