I was thinking, that when "add constraint" cannot choose appropriate
index, may be some explicit help (like ... using <index_name>;) would be
due.
Basically all the FK trigger does is:
SELECT EXISTS(SELECT 1 FROM pk_table WHERE pk_col1 = val1 AND pk_col2 = val2)
And fails if query returns false. The planner is still free to use the index or not to execute the query just as if you had written it by hand. For a small table fully in memory it likely would prefer a sequential scan and it would be perfectly within its rights to do so.
David J.
