#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    1
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    The created index will be used.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,911
    Rep Power
    1045
    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.

IMN logo majestic logo threadwatch logo seochat tools logo