From: Craig A. James <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Overriding the optimizer
Date: Thu, 15 Dec 2005 18:52:19 -0800
Message-ID: <43A22BE3.6020103@modgraph-usa.com> (raw)
In-Reply-To: <43A226E7.7040508@paradise.net.nz>
References: <200512141603.52583.blargity@gmail.com>
<7484.1134600423@sss.pgh.pa.us>
<1134629567.14248.27.camel@firebolt>
<200512150148.16125.blargity@gmail.com>
<1134644526.14248.60.camel@firebolt>
<43A1C40D.8040006@paradise.net.nz>
<43A1F6DB.3080805@modgraph-usa.com>
<43A226E7.7040508@paradise.net.nz>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cjames@modgraph-usa.com
Subject: Re: Overriding the optimizer
In-Reply-To: <43A22BE3.6020103@modgraph-usa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox