Re: indexes on primary and foreign keys

Burak Seydioglu <buraks78(at)gmail(dot)com> 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