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
