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

From Tom Lane
Subject Re: indexes on primary and foreign keys
Date
Msg-id 29042.1137020793@sss.pgh.pa.us
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:

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