Re: Performance problem on delete from for 10k rows. May - Mailing list pgsql-performance

From Dave Cramer
Subject Re: Performance problem on delete from for 10k rows. May
Date
Msg-id 4238492B.2000801@fastcrypt.com
Whole thread Raw
In response to Re: Performance problem on delete from for 10k rows. May  (Alvaro Herrera <alvherre@dcc.uchile.cl>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Performance problem on delete from for 10k rows. May
Next
From: David Gagnon
Date:
Subject: Re: Performance problem on delete from for 10k rows. May