From: Dave Cramer <pg@fastcrypt.com>
To: Alvaro Herrera <alvherre@dcc.uchile.cl>
Cc: 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 09:56:43 -0500
Message-ID: <4238492B.2000801@fastcrypt.com> (raw)
In-Reply-To: <20050316143511.GC21935@dcc.uchile.cl>
References: <42375281.9090102@siunik.com>
<20050315233834.GA68209@winnie.fuhr.org>
<4238322F.10302@siunik.com>
<20050316143511.GC21935@dcc.uchile.cl>
Really? Postgres is generating these queries ???
Dave
Alvaro Herrera wrote:
>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 ...
>
>
>
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
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: pg@fastcrypt.com, alvherre@dcc.uchile.cl, dgagnon@siunik.com, mike@fuhr.org
Subject: Re: Performance problem on delete from for 10k rows. May
In-Reply-To: <4238492B.2000801@fastcrypt.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