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)".