John, I think it's worth pointing out that Postgres most likely does a full table scan to validate a constraint by design and not in optimization oversight. Think of what's gonna happen if the index used for checking is corrupted?
This can't be true: a corrupted index is a failure mode, and failure modes are not expected in normal flow. Think of it this way: we must never use index scan, because if index is corrupted the results are going to be disastrous, so we will always do Seq Scans.
