Re: query optimiser changes 6.5->7.0

At 01:09 PM 02-06-2000 -0400, Tom Lane wrote:
>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> They can certainly turn of off before a query and on after the query,
>> right?
>
>Sure, that's how I'd suggest using it at the moment.
>
>> Seems people have been asking for fine-tuned optimizer control.
>
>I know, but I think that's the wrong direction to go in, for the reasons
>I tried to explain. The more specific the command you give to force
>a particular query plan, the more likely that command is to go wrong
>as the situation changes.

True.

But in very many cases the humans do better than the optimizer/planner.

How about a priority/rating/mode for indexes? And we won't have to deal
with legacy SQL statements containing "hints" scattered everywhere...

That's less blunt. But it's still so blunt that it may not be that useful
or work better than the global seq_scan setting.

Is it common for the optimizer to do stupid things? If it is, is there a
way for it to figure out it has done something stupid and to try not to do
it again?

e.g. Ooops, I actually went through 50% of the possible rows this time,
maybe that wasn't such a good idea. Try something else.

Or oops, my cost estimate was really off, revising estimate. I think this
would be preferable- assume the optimizer is not stupid, just ignorant.

Ack, it starts to look like a chess algorithm :).

Cheerio,

Link.