Re: indexes on primary and foreign keys

От: Tom Lane
Тема: Re: indexes on primary and foreign keys
Дата: ,
Msg-id: 29042.1137020793@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: indexes on primary and foreign keys  (Burak Seydioglu)
Список: pgsql-performance

Скрыть дерево обсуждения

indexes on primary and foreign keys  (Burak Seydioglu, )
 Re: indexes on primary and foreign keys  (Tom Lane, )
 Re: indexes on primary and foreign keys  (Michael Fuhr, )
  Re: indexes on primary and foreign keys  (Burak Seydioglu, )
  Re: indexes on primary and foreign keys  (K C Lau, )
   Re: indexes on primary and foreign keys  (Michael Glaesemann, )
    Re: indexes on primary and foreign keys  (Michael Fuhr, )
    Re: indexes on primary and foreign keys  (K C Lau, )
     Re: indexes on primary and foreign keys  (Tom Lane, )

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 по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: indexes on primary and foreign keys
От: Simon Riggs
Дата:
Сообщение: Re: Extremely irregular query performance