Re: CHECK constraints and optimizations

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Thu, May 06, 2004 at 09:02:21PM -0400, Tom Lane wrote:
>> Doubtless you could do it. The problem with the idea is that those
>> inference tests are pretty expensive.

> Is it really that expensive?

I'm afraid it would be. You're correct that the basic test in the
pred_test routine is simple enough, but there are a few issues:

1. You gotta root through all the index opclasses to see if you can find
one involving the operators at hand. (I'm pretty sure this is the most
expensive part of pred_test_simple_clause() as it stands.)

2. Using this to detect redundant/contradictory clauses implies
comparing every WHERE clause to every other WHERE clause, hence O(N^2)
effort.

3. You'll be paying the price even on pretty simple queries. I usually
figure that extra planner effort is worthwhile if it only fires on
complex queries, or queries where there's particular reason to think a
win might be had. (Eg, 7.4 goes out of its way when it sees an
IN-subselect clause, but I don't think anyone has a problem with that.)
But with this idea, I don't see any way to avoid expending a lot of
effort on queries where no win will actually result.

BTW, there actually is code in the btree index stuff to detect
contradictory index quals, so "x > 10 AND x < 10" will in fact result in
no I/O if the chosen plan is an indexscan on x. (This path doesn't have
the same problems mentioned above, because by the time control gets
there, we've already determined that the operators are indeed in the
same index opclass and that the same variable is involved.) So that's
another hole in the scope of usefulness of a planning-time test.

I was a tad surprised by the assertion up at the top of this thread
that MySQL has a test for this case. From what I know of their design
philosophy, they'd have even less interest than us in optimizing
badly-written queries at the cost of slowing down the normal path.
Am I right to guess that what they actually have is a short-circuit case
similar to ours for contradictory index quals, and not a blanket check
for contradictory WHERE conditions in general?

regards, tom lane