Does adding a foreign key to an indexed column boost performance?

Question

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?)

Answer 1

Yes, foreign keys can definitely improve performance of queries, but it depends on the Database you are using and often whether these keys are 'enforced' or not.

In Oracle and SQL Server having foreign keys definitely can increase performance when reading / joining multiple tables on their foreign key

Why? Having an checked/validated foreign key gives the query optimizer extra information regarding the relation 2 tables have.

It knows, that when a child table is inner joined to a parent table:

1. That the parent table has the same amount of records or less compared to the child table.
2. That all keys in the child exists in the parent.

This all helps the query optimizer in estimating the rows that are going to be processed. This esimation being right is really important for most (if not all) query optimizers.

Proof for this general fact can been seen by the recent addition of foreign keys in the form of metadata only to Hadoop Hive. The goal of this addition is do help the CBO (Cost Based Optimizer), this Hive Jira entry explains...

Furthermore, having (bitmap) indexes on foreign keys also improves performance in Oracle when using fact tables: 'A bitmap index should be built on each of the foreign key columns of the fact table or tables'. See the following link...

Foreign keys, for obvious reasons will cost you extra when inserting / updating data: extra work has to be done by the database compared with NOT having fk's

You can easily see this in SQL server (for example) by investigating Explain plans.

I do not know Postgresql, but my approach to validate the effect of FK's would be to look at explain plans. Do they differ when FK's are enabled / disabled / dropped?

[Edit] I actually found this proof that FK's can enable read performance in Postgresql but the reason for this is somewhat different: BECAUSE FK's are enabled, the query in the example can be changed to be more performant.

Answer 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.

Answer 3

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