Re: indexes on primary and foreign keys - Mailing list pgsql-performance

From Tom Lane
Subject Re: indexes on primary and foreign keys
Whole thread Raw
In response to indexes on primary and foreign keys  (Burak Seydioglu)
List pgsql-performance
Burak Seydioglu <> writes:
> I do a load of sql joins using primary and foreign keys. What i would like
> to know if PostgreSQL creates indexes on these columns automatically (in
> addition to using them to maintain referential integrity) or do I have to
> create an index manually on these columns as indicated below?

Indexes are only automatically created where needed to enforce a UNIQUE
constraint.  That includes primary keys, but not foreign keys.

Note that you only really need an index on the referencing (non-unique)
side of a foreign key if you are worried about performance of DELETEs
or key changes on the referenced table.  If you seldom or never do that,
you might want to dispense with the index.

            regards, tom lane

pgsql-performance by date:

From: Tom Lane
Subject: Re: Extremely irregular query performance
From: Michael Fuhr
Subject: Re: indexes on primary and foreign keys