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
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
