Re: Does "ON UPDATE" for foreign keys require index?

2019-04-25 Thread Alvaro Herrera 
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Does "ON UPDATE" for foreign keys require index?

2019-04-25 Thread rihad 
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