Re: CHECK constraints and optimizations

On Wed, 5 May 2004, Edmund Dengler wrote:

> Greetings!
>
> Just trying some tests out, and wanted to know about some optimizations.
> If I do a CHECK constraint on a table, is this used to optimize a SELECT
> or does Postgresql rely mostly on normal index search?

I think the only kind of constraint that incidentally improves performance
is a unique constraint, which creates a unique index.

A check constraint is run on a record when it is changed to make sure it
still meets the requirements of the constraint. There is no seperate file
that says "this row meets the constraint". Deferred constraints mean the
check is to be done at the commit time of the transaction.

Note that unique constraints are not necessarily deferrable due to issues
caused by using an immediate acting unique index. I don't think this is
easily fixable either.

So, a check constraint is of no use during a read from the table, and
is a performance penalty when writing to it.