Re: Optimizer picks an ineffient plan

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

> On Wed, 3 Sep 2003, Bupp Phillips wrote:
>
> > Well, it's unfortunate that you feel that way, because SQL Server handles it
> > correctly.
>
> For some definition of correctly.  If you're in a system which gets
> penalized .001 seconds for each query planning that uses a multi-column
> order by and you do 100 million of them that this doesn't apply to, and
> one that it does which save you 30 seconds, is that correct?

Uhm. Yes. Absolutely.

For OLTP systems like a web site if there's a single query anywhere in the
system that takes 30s the system is broken. Every time that query happens to
be hit a few times in a row the OLTP system will simply break. This isn't a
performance issue, it's outright broken.

Whereas a 1ms performance hit on every query plan will make virtually no
difference at all in performance and most importantly, it will work. At worst
it means provisioning a 1% faster machine. Even then only if my system isn't
preparing all these queries in advance, in which case I have bigger
performance and security issues than a 1ms per query hit.

For DSS systems handling queries that take minutes or hours to run the case is
even clearer. The 1ms hit is even less of an issue, and the 30s gain will
balloon and turn into minutes or hours of speedup.

I'm pretty sure this particular case was not one of the cases where people
said it just wasn't worth doing. This was just too hard. Solving it in a way
that integrates cleanly with postgres's aggregates will be very hard and
require someone who understands lots of different parts of postgres to spend
an awful lot of time thinking about the problem.

[This is one of the strength's of free software. There's no marketing
department providing checklists that have to be met even if there's no good
solution today. So instead of a bad solution that sticks around for a long
time, we'll one day (hopefully) have a good solution when someone figures out
how to do it.]

--
greg