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

On Thu, 24 Apr 2003, Robert Dyas wrote:

> clause restriction on a column). But the concept is still the same - don't
> spend time joining rows when you are going to later throw them away becuase
> of some where clause restriction. Stated another way, "restrict then join"
> is far more efficient than "join then restrict".

True, but "join then restrict" is guaranteed to get the right answer
through sheer force of will, while restrict then join requires that you
not only try to optimize the query, but you have to make sure you are not
throwing away the wrong ones. I.e. accidentally leaving in extra rows to
throw away costs you CPU time, accidentally tossing the wrong rows gives
bogus results.

The real answer here is that SQL is the answer. It allows you to restrict
the data sets you're playing with before the join, not after. Subselects,
unions, etc... allow you to build a more efficient query that is handling,
in theory, smaller data sets, and should therefore be faster.

I'd love for the planner to be able to optimize everything, but let's face
it, since all databases live in the real world where optimzation can never
be perfect, we should all strive to create SQL queries that hit the fewest
rows needed to do the job, and THEN let the planner take it from there.

We all benefit from faster sorting algorhythms, better indexing methods,
etc. Only people who write very inneficient SQL benefit from the types
of optimizations you're talking about. So, if someone has to put in time
programming, I'd rather it be on things we can all use and benefit from
the most.

When we have async/sync multi-master multi-slave replication, with bit
mapped indexes, and super fast hashing, along with maybe a ccNUMA friendly
caching method that can efficiently handle tens of gigabytes of free RAM,
sure, maybe someone should get around to optimizing the corner cases. But
unconstrained joins or even just poorly thought out ones, are NOT a corner
case, they're a SQL mistake.