Re: Indexing Foreign Key Columns

[Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net>]

On Tue, 28 Aug 2007 13:19:32 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.)

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