Indexing Foreign Key Columns
I am curious if there are any rules of thumb for when to index a
foreign key column? I was under the impression that it was always a
good idea to do this based on the fact that you typically join
through a foreign key but after reading the docs I'm not so sure it's
necessary or provides any improvements to join query speed.
Thanks,
Josh
Josh Trutwin <josh@trutwins.homeip.net> writes:
I am curious if there are any rules of thumb for when to index a
foreign key column?
(You realize of course that there's already an index on the referenced
column, else you wouldn't have been allowed to reference it.)
You need an index on the referencing column unless the referenced table
is pretty static: DELETEs in the referenced table will be real slow
without it, and also UPDATEs that change the referenced column. However
there are applications where this never happens, or so infrequently that
it's not worth paying to maintain an extra index on the referencing
table.
As far as actual joins go, the only case where an index on the
referencing column is likely to be tremendously useful is where you are
selecting a small number of rows using a constraint on the *referenced*
table. For instance
select ... from pktable left join fktable on (pkcol = fkcol)
where pktable.somecol = something
In this situation a sensible plan is a nestloop with the pktable on the
outside (perhaps searched via an index on somecol) and then using an
index on fkcol to probe into fktable for matches.
If you don't do anything like that, and you don't change or delete pk
keys, then you probably don't need an index.
regards, tom lane
On Tue, 28 Aug 2007 13:19:32 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Josh Trutwin <josh@trutwins.homeip.net> writes:
I am curious if there are any rules of thumb for when to index a
foreign key column?(You realize of course that there's already an index on the
referenced column, else you wouldn't have been allowed to reference
it.)
Certainly - If there were no index on the referenced PK / UNIQUE col
I'd be concerned.
You need an index on the referencing column unless the referenced
table is pretty static: DELETEs in the referenced table will be
real slow without it, and also UPDATEs that change the referenced
column. However there are applications where this never happens,
or so infrequently that it's not worth paying to maintain an extra
index on the referencing table.
I would say my app falls into this category. Thanks for your help,
Josh