>> Lastly, in most cases knowing that t2.id <= 10 is just not worth all >> that much; it's certainly far less useful than an equality condition.
> I'd have thought that my link to a thread of a reported 1100 to 2200 times > performance improvement might have made you think twice about claiming the > uselessness of this idea.
Personally, I think this optimization is a long way down the list of important items because I don't see it as a typical use case. There are already patches submitted for more important items, so this isn't the right place to be arguing such things. Not every beneficial optimization has a wide use case.
Since the discussion has become more general, I would add a few points.
I said "in most cases". You can find example cases to support almost any weird planner optimization no matter how expensive and single-purpose; but that is the wrong way to think about it. What you have to think about is average cases, and in particular, not putting a drag on planning time in cases where no benefit ensues. We're not committing any patches that give one uncommon case an 1100X speedup by penalizing every other query 10%, or even 1%; especially not when there may be other ways to fix it.
I agree with this point also, I just don't see it as a blocker for expensive general case optimizations.
There are many optimizations we might adopt, yet planning time is a factor. It seems simple enough to ignore more complex optimizations if we have already achieved a threshold cost (say 10). Such a test would add nearly zero time for the common case. We can apply the optimizations in some kind of ordering depending upon the cost, so we are careful to balance the cost/benefit of trying certain optimizations.
Optimizer directives might be useful also, but we can do just as well with a heuristic.
--
Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services