Date: Fri, 16 Dec 2005 15:31:03 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Overriding the optimizer
In-reply-to: <43A1F6DB.3080805@modgraph-usa.com>
To: "Craig A. James" <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Message-id: <43A226E7.7040508@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
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