September 20th, 2013, 05:43 AM
-
Foreign Key Index question
Hi,
Probably a silly question. When I create a foreign key on a table, I note an index is created on the foreign key column.
If I use that same column for other querying/searches, is it necessary to create a seperate index on that same column, or is the foreign key index already serving the purpose?
Thanks,
John.
October 8th, 2013, 08:20 AM
-
The created index will be used.
October 8th, 2013, 08:44 AM
-
Hi,
Originally Posted by johnsg6977
When I create a foreign key on a table, I note an index is created on the foreign key column.
No, it's not:
Originally Posted by http://www.postgresql.org/docs/9.3/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
[...] declaration of a foreign key constraint does not automatically create an index on the referencing columns.
Originally Posted by johnsg6977
If I use that same column for other querying/searches, is it necessary to create a seperate index on that same column, or is the foreign key index already serving the purpose?
Apart from the fact that PostgreSQL does not automatically create that index: It depends on the concrete query. There are many different types of indexes for different purposes. You may be able to use the same index for a foreign key constraint as well as a particular query. But there's no guarantee for it, and it may not be optimal.