From: | rihad <rihad(at)mail(dot)ru> |
---|---|
To: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Does "ON UPDATE" for foreign keys require index? |
Date: | 2019-04-25 09:29:31 |
Message-ID: | 6cb9e903-b7c7-23c1-65b4-5df8dbdfc1db@mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2019-04-25 13:11:57 | Re: Does "ON UPDATE" for foreign keys require index? |
Previous Message | Michel Pelletier | 2019-04-25 02:26:30 | Re: how to add more than 1600 columns in a table? |