Re: on update / on delete performance of foreign keys

Richard Huxton wrote:
> Florian G. Pflug wrote:
>> 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.
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...

Still, I believe that even with an index, the performance will suffer
when deleting a lot of rows from an referenced tabled, because for each
row a trigger has to fire, and do an index scan.
It's entirely possible though, that this is already optimized, and I
just misread the code ;-)

greetings, Florian Pflug