Re: query optimiser changes 6.5->7.0

Martijn van Oosterhout <kleptog(at)cupid(dot)suninternet(dot)com> writes:
>>>> Isn't there a way to tell the optimizer to use an index scan if one
>>>> knows this is going to be the best.
>>
>> set enable_seqscan=off;

> But doesn't this mean that if there are no appropriate
> indicies defined on a table, queries will fail?

No. It just biases the planner heavily against choosing a seqscan
if there are any indexscan alternatives available.

A more serious objection is that this switch is a very blunt instrument,
since it's going to affect planning for *all* queries as long as it's
off. It's not really designed to be used as anything except a debugging
tool --- if you run with it routinely, you will almost certainly lose
more in performance on queries that shouldn't have been seqscanned than
you gain on the ones where the planner would have made the wrong choice.

In the long run the right answer is to continue to work on improving
the planner. I don't much like the idea of user overrides on planner
choices as a standard answer --- the trouble with that is that when
you throw "set enable_seqscan=off" or some such into an application,
it tends to stay there long after the need for it is gone; perhaps long
enough for the database schema to be revised to the point where the
command forces use of a plan much worse than what the planner would
have picked without such "help". This problem gets rapidly worse with
more-specialized user controls, such as a command to force use of a
specific index.

So, while I have been known to suggest "set enable_seqscan=off" and
friends when there seemed no other short-term answer, I don't want to
enshrine it as a standard recommendation. It's just a way of plugging
holes in the dike until more planner work gets done.

regards, tom lane