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.