From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: on update / on delete performance of foreign keys |
Date: | 2005-01-24 14:39:09 |
Message-ID: | 41F5088D.8060702@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2005-01-24 14:42:34 | Re: on update / on delete performance of foreign keys |
Previous Message | Greg Stark | 2005-01-24 14:30:02 | Re: on update / on delete performance of foreign keys |