On Thu, May 06, 2004 at 09:29:42AM -0600, scott.marlowe wrote:
> 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.
I have been thinking though, imagine a table with the constraint:
x < 1000
If I have a query that has WHERE x > 2000, can't that be optimised to
WHERE FALSE? Or WHERE x < 1200 optimised to x < 1000?
Obviously not if the constraint is deferred, but otherwise?
The other person is correct in that (x < 1000 and x > 2000) is not
optimised away by postgresql. Odd, because the capability is there as
very similar tests are use by partial indexes and the index code in
general. If that worked, the system could just add the (simple) CHECK
constraints to the WHERE clause of a query, do the optimisation phrase
and then remove any that remain.
I can't see why this wouldn't work.
Any thoughts?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.