Re: Performance problem on delete from for 10k rows. May

From: Alvaro Herrera
Subject: Re: Performance problem on delete from for 10k rows. May
Date: ,
Msg-id: 20050316143511.GC21935@dcc.uchile.cl
(view: Whole thread, Raw)
In response to: Re: Performance problem on delete from for 10k rows. May  (David Gagnon)
Responses: Re: Performance problem on delete from for 10k rows. May  (Dave Cramer)
Re: Performance problem on delete from for 10k rows. May  (David Gagnon)
List: pgsql-performance


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)


pgsql-performance by date:

From: "Rodrigo Moreno"
Date:
Subject: Help to find out problem with joined tables
From: Stephan Szabo
Date:
Subject: Re: Performance problem on delete from for 10k rows. May