Re: Indexing Foreign Key Columns

Josh Trutwin <josh(at)trutwins(dot)homeip(dot)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