Re: performance regression, 7.2.3 -> 7.3b5 w/ VIEW

On Wed, Nov 13, 2002 at 08:58:04AM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm(at)rice(dot)edu> writes:
> > Bingo, that solved it. I'm back to 160 ms. What does Tom feel about
> > removing this? Is there some way the planner could have known which
> > was the smarter/faster order of application?
>
> As I said in the previous thread, I don't have a lot of patience with
> the notion of expecting the planner to promise anything about evaluation
> order of WHERE clauses. I wasn't thrilled with adding the patch, but
> I'm even less thrilled with the idea of backing it out now.

Having read the previous thread, I realized you wouldn't be thrilled
about it, that's why I asked. While I agree in principle (don't promise
a particular order), the pragmatic corollary of that principle would say
if you don't favor a particular order, then don't change the order from
previous stable releases.

Unlike the previous thread, I'm not looking for a particular order:
there're no side-effects I'm trying to exploit, I just want the best
possible performance.

> There has been some discussion of reordering WHERE clauses based on
> estimated cost --- a simple form of this would be to push any clauses
> involving subplans to the end of the list. I haven't done anything
> about that yet, mainly because I'm unsure if there are cases where it
> would be worse than not doing it.

Me either, though my gut says subplans are expensive. I _can_ trivially
write queries that do the wrong thing (suboptimal order of WHERE clauses)
with or without this patch.

It's clearly the wrong time to try to do anything fancier, but the
conservative thing to do (in my unbiased opinion ;-) is put it back
the way it was for the last stable release, on the principle of least
surprise - there seems to be no bug fixed or functionality gained by
keeping the change.

Seems like this is at least worth a TODO:

* Examine WHERE clause order optimization possibilities, particularly
with subplans

Ross