One can use ALTER TABLE ADD FOREIGN KEY ... NOT VALID to skip checking the FK constraint on existing rows at creation time (and only have subsequently inserted/updated rows checked).

Aside from the issue of referential integrity, it seems plausible to me that the query planner could use information about FK constraint validity to optimise joins (e.g. doing index scans/hash joins vs parallel seq scans). So, does it? And what does the planner do if the constraint is NOT VALID? (E.g. does it always fall back to a seq scan? Does it only join rows that are marked 'valid' on LEFT JOIN etc).

The docs seem silent on this issue. Several questions kind of allude to there being performance implications (e.g. here, here), but I don't feel like I can't find an answer that definitively says either "this is just about referential integrity" or "this will indeed affect JOIN performance (or JOIN results, or have other performance-related side-effects)".

1 Answer 1


The optimizer uses foreign key constraints less than you might think. They can improve the row count estimate for a join. That won't improve the performance of the join, but a good row count estimate will make PostgreSQL make better plan choices further up.

Invalid constraints are not considered for that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.