Martijn van Oosterhout <kleptog@svana.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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
