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

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)