To: Mark Kirkwood <markir@paradise.net.nz>
cc: "Craig A. James" <cjames@modgraph-usa.com>,
	pgsql-performance@postgresql.org
Subject: Re: Simple join optimized badly? 
In-reply-to: <4529C43A.3050503@paradise.net.nz> 
References: <45274A77.7080005@modgraph-usa.com>
	<18722.1160236300@sss.pgh.pa.us>
	<5f820f750610071850o6ed0b19o162d65d1987b2a8c@mail.gmail.com>
	<200610081605.03501.josh@agliodbs.com>
	<4529A0CA.8000904@modgraph-usa.com>
	<4529B6D0.2010206@paradise.net.nz>
	<4529B7E8.8080606@modgraph-usa.com>
	<4529C43A.3050503@paradise.net.nz>
Comments: In-reply-to Mark Kirkwood <markir@paradise.net.nz>
	message dated "Mon, 09 Oct 2006 16:38:34 +1300"
Date: Mon, 09 Oct 2006 00:43:55 -0400
Message-ID: <19817.1160369035@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Kirkwood <markir@paradise.net.nz> writes:
> True enough - but (aside from the fact that hints might take just as 
> long to get into the development tree as cost-for-functions might take 
> to write and put in...) there is a nasty side effect to adding hints - 
> most of the raw material for optimizer improvement disappears (and hence 
> optimizer improvement stalls)- why? simply that everyone then hints 
> everything - welcome to the mess that Oracle are in (and seem to be 
> trying to get out of recently)!

And *that* is exactly the key point here.  Sure, if we had unlimited
manpower we could afford to throw some at developing a hint language
that would be usable and not too likely to break at every PG revision.
But we do not have unlimited manpower.  My opinion is that spending
our development effort on hints will have a poor yield on investment
compared to spending similar effort on making the planner smarter.

Josh's post points out some reasons why it's not that easy to get
long-term benefits from hints --- you could possibly address some of
those problems, but a hint language that responds to those criticisms
won't be trivial to design, implement, or maintain.  See (many) past
discussions for reasons why not.

			regards, tom lane