Thread: Update and foreign key indices

Hello Folks,

I will ask my question through an example:


1) I have table PROJECT that has a foreign key USER_ID references USER(ID)

2) I *don't* use USER_ID in a where clause so don't have to create indices for performance reason. 

3) USER table's primary key *never* changes.

Q: Do I have to create an index for FK USER_ID to speed up updates to USER table rows?


Basically, is PG nice enough to understand that since a key is not updated, it need not check the integrity
constraints.


I am using PGSQL 7.1.3

Thanks.

On Mon, 5 Aug 2002, Nikhil G. Daddikar wrote:

> Hello Folks,
>
> I will ask my question through an example:
>
>
> 1) I have table PROJECT that has a foreign key USER_ID references
> USER(ID)
>
> 2) I *don't* use USER_ID in a where clause so don't have to create
> indices for performance reason.
>
> 3) USER table's primary key *never* changes.
>
> Q: Do I have to create an index for FK USER_ID to speed up updates to
> USER table rows?
>
>
> Basically, is PG nice enough to understand that since a key is not
> updated, it need not check the integrity constraints.

It should not run the select if the old value of the key equals the
new one (for each row). I don't have 7.1 to check, but running the
query would cause obvious broken behavior so I figure it probably
hasn't since inception.