Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> If you have a foreign key on a column, then whenever the primary key is
>> modified, the following checks may occur:
>>
>> * Check to see if the child row exists (no action)
>> * Delete the child row (cascade delete)
>> * Update the child row (cascade update)
>>
>> All of which will benefit from an index...
> OK, then perhaps we should be creating an index automatically? Folks?
We should not *force* people to have an index. If the master table very
seldom changes, then an index on the referencing table will be a net
loss (at least as far as the foreign-key ops go). You'll pay for it on
every referencing-table update, and use it only seldom.
Possibly there should be an entry in the "performance tips" chapter
recommending that people consider adding an index on the referencing
column if they are concerned about the speed of updates to the
referenced table. But I dislike software that considers itself smarter
than the DBA.
regards, tom lane