Re: Optimizer picks an ineffient plan

Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> 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.

> Well I'm sure the same arguments were made 30 years ago about optimizing
> compilers. But thankfully the optimizer-geeks won the argument.

Um ... I *am* an optimizer-geek.  You can find my name in the credits
for Bliss/11, which was the best optimizing compiler on the planet about
thirty years ago.  I stand by my comment that there's a tradeoff between
the potential gain from an optimization and the time spent to find it.

PG is at a disadvantage compared to typical compilation scenarios, in
that a compiler assumes its output will be executed many times, while
SQL queries often are planned and then executed but once.  There's been
some talk of working harder when planning a "prepared statement", but
so far I've not seen very many places where I'd really want to alter
the planner's behavior on that basis.

            regards, tom lane