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.