'Does adding a foreign key to an indexed column boost performance?
A colleague claims that he used foreign keys to optimize queries in past. I think that foreign keys are used only when inserting or updating data in a table. I don't see how they can be used to speed up the search.
How can a foreign key help when creating an execution plan? Am I missing something? If so, then under what circumstances does it help?
(We use PostgreSQL, which I don't have much experience with. Is it possible that it behaves differently than, say, Oracle or MySQL?)
Solution 1:[1]
Primary Keys and Unique constraints create a corresponding INDEX. But not FK constrains:
declaration of a foreign key constraint does not automatically create an index on the referencing columns.
https://www.postgresql.org/docs/current/static/ddl-constraints.html
So you are right. But often it is a good idea to have an index on FK
Solution 2:[2]
Without an index on foreign key(s) the whole child table needs to be scanned to verify if any rows are referencing the key(s) you're trying to delete or update in the parent (i.e. foreign) table.
So yes, in this particular case having indexes will greatly improve performance.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Vao Tsun |
| Solution 2 |