Re: Overriding the optimizer

Mark Kirkwood wrote:
> I hear what you are saying, but to use this fine example - I don't know 
> what the best plan is - these experiments part of an investigation to 
> find *if* there is a better plan, and if so, why Postgres is not finding 
> it.
> 
>> There isn't a database in the world that is as smart as a developer, 
>> or that can have insight into things that only a developer can 
>> possibly know.
> 
> That is often true - but the aim is to get Postgres's optimizer closer 
> to developer smartness.

What would be cool would be some way the developer could alter the plan, but they way of doing so would strongly encourage the developer to send the information to this mailing list.  Postgres would essentially say, "Ok, you can do that, but we want to know why!"

> After years of using several other database products (some supporting 
> hint type constructs and some not), I have come to believe that hinting 
> (or similar) actually *hinders* the development of a great optimizer.

I agree.  It takes the pressure off the optimizer gurus.  If the users can just work around every problem, then the optimizer can suck and the system is still usable.

Lest anyone think I'm an all-out advocate of overriding the optimizer, I know from first-hand experience what a catastrophe it can be.  An Oracle hint I used worked fine on my test schema, but the customer's "table" turned out to be a view, and Oracle's optimizer worked well on the view whereas my hint was horrible.  Unfortunately, without the hint, Oracle sucked when working on an ordinary table.  Hints are dangerous, and I consider them a last resort.

Craig