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?
Asked
Active
Viewed 1,056 times
2
-
1Have you ran any tests and found any difference? – Kuberchaun Oct 12 '12 at 23:08
-
yes I have but I wanted to make sure it wasn't just me. – matthewaveryusa Oct 12 '12 at 23:23
-
2You can test with `EXPLAIN ANALYZE` to find out. You'll discover that they have exactly the same query plans and exactly the same execution performance. – Craig Ringer Oct 13 '12 at 00:59
-
Related: http://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys – Highly Irregular Mar 25 '13 at 01:31
1 Answers
4
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.
JohnLBevan
- 22,735
- 13
- 96
- 178
-
["a foreign key must reference columns that either are a primary key or form a unique constraint"](http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK) and ["Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint."](http://www.postgresql.org/docs/current/static/ddl-constraints.html#AEN2470). So an FK implies that the index exists. An INSERT will have to update the index and the FK will use the index to check integrity. – mu is too short Oct 12 '12 at 23:24
-
2@muistooshort, a FK implies an index exists in the other table. The dox also state "declaration of a foreign key constraint does not automatically create an index on the referencing columns". – Highly Irregular Mar 25 '13 at 01:27
-
@HighlyIrregular: Right. But the FK implies the existence of an index that can be used to check that the FK is satisfied when doing INSERTs or UPDATEs on the referencing table. You'd want an index on the referencing table to make checking the FK quicker when UPDATEs or DELETEs are done on the referenced table. Right? – mu is too short Mar 25 '13 at 01:54
-
1@muistooshort, yes. The point is that you need to create it; creating the FK doesn't take care of that for you. – Highly Irregular Mar 25 '13 at 02:19