From: Mark Kirkwood <markir@paradise.net.nz>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Overriding the optimizer
Date: Fri, 16 Dec 2005 15:31:03 +1300
Message-ID: <43A226E7.7040508@paradise.net.nz> (raw)
In-Reply-To: <43A1F6DB.3080805@modgraph-usa.com>
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>
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
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: markir@paradise.net.nz, cjames@modgraph-usa.com
Subject: Re: Overriding the optimizer
In-Reply-To: <43A226E7.7040508@paradise.net.nz>
* 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