CODEWITHSUNDEEP
postgresql
matthewaveryusa
matthewaveryusa

Reputation: 652

Is there a performance difference between joining on two indexed tables vs a foreign key?

Assuming that referential integrity is out of the picture, what are the differences (especially in terms of performance) between a join on 2 indexed columns vs a join on two indexed columns, one of which having been defined as referencing the other?

Upvotes: 2

Views: 1307

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24490

It looks like performance gains only come from adding an index; not from the foreign key itself (i.e. the foreign key only provides referential integrity).

http://postgresql.1045698.n5.nabble.com/indexes-on-primary-and-foreign-keys-td2054279.html

You'll probably take a slight performance hit on inserts (and updates involving the foreign key field), as the system will need to validate that the item exists in the referenced table.

Upvotes: 4

Related Questions