From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | rihad <rihad(at)mail(dot)ru> |
Cc: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Does "ON UPDATE" for foreign keys require index? |
Date: | 2019-04-25 13:11:57 |
Message-ID: | 20190425131157.GA15634@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2019-Apr-25, rihad wrote:
> 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.
We have an optimization that if you update a row in a transaction just
once, and the column is not modified, then it won't need to scan the
referencing table. However, if you make two updates in a transaction,
the optimization isn't smart enough to detect that the FK isn't
invalidated, so a scan will occur. Therefore, if A is large [enough
that you care about a seqscan on it] and you expect to be doing more
than one UPDATE of B in the same transaction, then this could be
noticeable.
I suggest you run some tests, just to be sure.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Kováčik | 2019-04-25 14:37:46 | analyze causes query planner to choose suboptimal plan for a select query in separate transaction |
Previous Message | rihad | 2019-04-25 09:29:31 | Does "ON UPDATE" for foreign keys require index? |