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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
