Re: Optimizer picks an ineffient plan

Tom Lane <tgl@sss.pgh.pa.us> writes:

> > However I'm not sure I see a lot of cases where this would come up.
>
> Yes, that's the real crux of the matter.  Should the optimizer spend
> cycles on *every* query to detect cases where the user has written
> useless sort keys?  I've got grave doubts that it's a win.  ISTM such
> an optimization penalizes the folk who write their queries well to
> benefit those who are careless.

Well I'm sure the same arguments were made 30 years ago about optimizing
compilers. But thankfully the optimizer-geeks won the argument. As a result
these days we can more or less write our code in whatever form is most
readable and flexible. We can spend our time worrying about algorithmic
improvements and design abstraction, and not worry that the extra layer of
abstraction will introduce redundant code or inefficient expressions.

Already today we see database-independent toolkits that write SQL queries for
you. They introduce needless subqueries and other inefficiencies willy-nilly.

I argue that if the performance of the database is important down to a
sub-second constant term per query, then you're talking about an OLTP system
where all the queries ought to be prepared and the plans cached. If you're
talking about a system where queries are constructed ad-hoc for every
execution then you should be talking about a DSS system running batch jobs
where an extra few seconds spent optimizing could save you hours.

All that said I'm not sure the case at hand is a great example. I don't think
it would be a big performance loss, but the added code complexity for nothing
might be annoying. I don't see how even automatically generated code is likely
to generate this situation.

--
greg