Date: Mon, 09 Oct 2006 16:38:34 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Simple join optimized badly?
In-reply-to: <4529B7E8.8080606@modgraph-usa.com>
To: "Craig A. James" <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Message-id: <4529C43A.3050503@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Thunderbird 1.5.0.7 (X11/20061003)
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>

Craig A. James wrote:

> 
> Perhaps you scanned past what I wrote a couple paragraphs farther down.  
> I'm going to repeat it because it's the KEY POINT I'm trying to make:
> 
> Craig James wrote:
>> Now you might argue that function-cost needs to be added to the 
>> optimizer's arsenal of tricks.  And I'd agree with you: That WOULD be 
>> a better solution than hints.  But I need my problem solved TODAY, not 
>> next year.  Hints can help solve problems NOW that can be brought to 
>> the PG team's attention later, and in the mean time let me get my 
>> application to work.

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)!

I understand that it is frustrating to not have the feature you need now 
  - but you could perhaps view it as a necessary part of the community 
development process - your need is the driver for optimizer improvement, 
and it can take time.

Now ISTM that hints "solve" the problem by removing the need any further 
optimizer improvement at all - by making *you* the optimizer. This is 
bad for those of us in the DSS world, where most ad-hoc tools do not 
provide the ability to add hints.

Cheers

Mark