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.
Attachment:
pgp00188.pgp
Description: PGP signature
