Re: enforcing a plan (in brief)

> On Thu, 2005-02-10 at 14:37 -0500, Bruce Momjian wrote:
>> No, we feel that is of limited value. If the optimizer isn't doing
>> things properly, we will fix it.
>
> I agree that improving the optimizer is the right answer for normal
> usage, so I can't get excited about query-level plan hints, but I can
> see the capability to instruct the planner being useful in an academic
> context.
>

I think that is sort of arrogant. Look at Oracle, you can give the planner
hints in the form of comments.

The idea that constructing a planner that will always do the best job is
like creating a program that can predict the weather. There are too many
subtle variations in datasets that are impossible to really evalute. I
posted a message last week called "One Big trend vs multiple smaller
trends." and you'll see what I mean.

Yea, on a simple data organization, you could make a great planner, but
someone who has studied the nature of their data can almost always toss
their hands up in frustration because the planner isn't working right.

I have had multiple issues with the inability to guide the planner on its
decisions. I'll give a couple examples:

A music database where the artist name is "Various Artists," given any
normal database of music recordings, "Various Artists" will be *THE* most
popular artist, usually close to almost half the data. Most of the time
I've had to turn off sequential scans for these queries. (I filter out
"various artists") Being able to say:

select * from cdtitles where artist = 'foo' /* index scan
ctitles_artist_ndx */ ;

Would be helpful as disabling sequential scan isn't always the right think
either.

The whole "Query optimizer 8.0.1 (and 8.0)" series of posts show a
different problem.

It all comes down to that the planner *can not* be perfect, and thus will
always be lacking in some respect. This is because you can not anticipate
every physical data storage pattern, therefore, the analyzer will not
correctly characterize them, and the planner will not create an optimal
plan.

Allowing the user to suggest alternate query strategies is a good idea.