Re: query optimization scenarios 17,701 times faster!!!

On Thu, 24 Apr 2003, Robert Dyas wrote:

> I was hoping to point out an extreme example in hopes of showing that some
> very important optimizations are not taking place.
>
> In a perfect world, two different queries that must by definition return the
> same result would run the exact same query plan, which would be the fastest
> one available. Yes, I know I'm stating the obvious, but sometimes that
> helps.

Of course in a perfect world doing the optimizations would take no time.
;)

> There are some basic optimizations that I think may not be taking place
that
> could have a dramatic impact in many different scenarios. The biggest one
> that I can see could be stated like this: "if a where clause includes a
> restriction on a primary key column with a fixed value, only that row should
> be used in subsequent query processing"

The particular conversion you're doing for that query requires more than
just that AFAICS. The outerness of the join makes it so you know there
won't be 0 rows after the join, and the distinct cancels out the
possibility of multiple rows. Both of those conditions seem necessary to
make the conversion valid as well. And for your first example (with
the right join), it seems that you'd need to know that if there were
multiple matching rows with the right join that the output rows were
guaranteed to be distinct (since otherwise the select distinct might roll
the two rows together which would cause the output to change from the
second version of that example). A more detailed example of what the
actual optimizations you're invisioning (rather than example queries)
might be helpful.

Also, I think there exists some chance of side effects that would be
elided by the second version in the case of views or set returning
functions, although I think we can probably ignore that.