Does "ON UPDATE" for foreign keys require index?

Поиск
Список
Период
Сортировка
От rihad
Тема Does "ON UPDATE" for foreign keys require index?
Дата
Msg-id 6cb9e903-b7c7-23c1-65b4-5df8dbdfc1db@mail.ru
обсуждение исходный текст
Ответы Re: Does "ON UPDATE" for foreign keys require index?  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Список pgsql-general

Hi. Say I have column A.b_id which references B.id (which is a primary key) and as such it is declared as a foreign key constraint. A.b_id has no index because it doesn't need one. What happens when table B's rows are modified (but never deleted)? Will PG still have have to scan A fo find A.b_id to do nothing with it? )) B.id itself is never modified, it's just a normal serial value typically used for id's.


The docs are a bit ambiguous:

Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.



https://www.postgresql.org/docs/9.6/ddl-constraints.html

В списке pgsql-general по дате отправления:

Предыдущее
От: Michel Pelletier
Дата:
Сообщение: Re: how to add more than 1600 columns in a table?
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: Does "ON UPDATE" for foreign keys require index?