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
