Re: Overriding the optimizer

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux