Re: Overriding the optimizer

Craig A. James wrote:
> I asked a while back if there were any plans to allow developers to
> override the optimizer's plan and force certain plans, and received a
> fairly resounding "No".  The general feeling I get is that a lot of work
> has gone into the optimizer, and by God we're going to use it!
>
> I think this is just wrong, and I'm curious whether I'm alone in this
> opinion.
>
> Over and over, I see questions posted to this mailing list about
> execution plans that don't work out well.  Many times there are good
> answers - add an index, refactor the design, etc. - that yield good
> results.  But, all too often the answer comes down to something like
> this recent one:
>
>   > Right on. Some of these "coerced" plans may perform   > much better.
> If so, we can look at tweaking your runtime
>   > config: e.g.
>   >
>   > effective_cache_size
>   > random_page_cost
>   > default_statistics_target
>   >
>   > to see if said plans can be chosen "naturally".
>
> I see this over and over.  Tweak the parameters to "force" a certain
> plan, because there's no formal way for a developer to say, "I know the
> best plan."
>

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.

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.


Best wishes

Mark