From: Alvaro Herrera <alvherre@dcc.uchile.cl>
To: David Gagnon <dgagnon@siunik.com>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem on delete from for 10k rows. May
Date: Wed, 16 Mar 2005 10:35:11 -0400
Message-ID: <20050316143511.GC21935@dcc.uchile.cl> (raw)
In-Reply-To: <4238322F.10302@siunik.com>
References: <42375281.9090102@siunik.com>
<20050315233834.GA68209@winnie.fuhr.org>
<4238322F.10302@siunik.com>
On Wed, Mar 16, 2005 at 08:18:39AM -0500, David Gagnon wrote:
David,
> I rerun the example with the debug info turned on in postgresl. As you
> can see all dependent tables (that as foreign key on table IC) are
> emptied before the DELETE FROM IC statement is issued. For what I
> understand the performance problem seem to came from those selects that
> point back to IC ( LOG: statement: SELECT 1 FROM ONLY "public"."ic" x
> WHERE "icnum" = $1 FOR UPDATE OF x). There are 6 of them. I don't know
> where they are comming from.
I think they come from the FK checking code. Try to run a VACUUM on the
IC table just before you delete from the other tables; that should make
the checking almost instantaneous (assuming the vacuuming actually
empties the table, which would depend on other transactions).
It would be better to be able to use TRUNCATE to do this, but in 8.0 you
can't if the tables have FKs. 8.1 is better on that regard ...
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)
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: alvherre@dcc.uchile.cl, dgagnon@siunik.com, mike@fuhr.org
Subject: Re: Performance problem on delete from for 10k rows. May
In-Reply-To: <20050316143511.GC21935@dcc.uchile.cl>
* 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