Florian G. Pflug wrote:
> Hi
>
> I ran into some performance problems regarding foreign keys lately.
> My schema has about 20 tables, which each contain from 10 to 100.000
> records. They have quite complicated interdependencies, modeled using
> foregin keys set to "on update cascade, on delete cascade".
> The schema stores data for multiple customers - Recently I wanted
> to extract the data for just a single customer. I duplicated the schema,
> and deleted all but one customer from the "customer" table. This worked
> as expected, but the delete took a few hours (!) on a moderatly fast
> machine (dual 1GHz PIII, RAID5-Array for postgres-data).

PostgreSQL doesn't automatically add indexes to foreign-key columns.
That sounds like the issue to me.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Harald Fuchs <hf0722x@protecting.net> writes:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>>> PostgreSQL doesn't automatically add indexes to foreign-key
>>> columns. That sounds like the issue to me.

>> Oh... *feeling a bit stupid*... Seems that I got confused, because it
>> requires an index to exist on the referenced table (To speed up inserts,
>> updates), but not on the referencing table...

> That's not quite right. What PostgreSQL (or any other DBMS) requires
> from the referenced table is a UNIQUE constraint on the column in
> question so that the referencing table points to a single row, and
> UNIQUE constraints are usually implemented by indices.

You missed the point. PG does force you to put an index on the
referenced column, but it does not force you to put one on the
referencing column. However, deletions in the referenced table are
going to be really slow if there is no index on the referencing column,
because there's no fast way to look up referencing rows matching the
key to be deleted.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend