Re: [PATCH] Equivalence Class Filters

On 6 December 2015 at 16:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>> 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/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services