Message-ID: <452EAE80.5030501@tweakers.net>
Date: Thu, 12 Oct 2006 23:07:12 +0200
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: Jeff Davis <pgsql@j-davis.com>
CC: Csaba Nagy <nagy@ecircle-ag.com>, "Jim C. Nasby" <jim@nasby.net>,
	postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Hints proposal
References: <20061012151439.GT28647@nasby.net>	
	<1160671375.31966.84.camel@dogma.v10.wvs>	
	<1160673340.28751.83.camel@coppola.muc.ecircle.de>
	<1160680031.31966.128.camel@dogma.v10.wvs>
In-Reply-To: <1160680031.31966.128.camel@dogma.v10.wvs>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 12-10-2006 21:07 Jeff Davis wrote:
> On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote:
> 
> To formalize the proposal a litte, you could have syntax like:
> 
> CREATE HINT [FOR USER username] MATCHES regex APPLY HINT some_hint;
> 
> Where "some_hint" would be a hinting language perhaps like Jim's, except
> not guaranteed to be compatible between versions of PostgreSQL. The
> developers could change the hinting language at every release and people
> can just re-write the hints without changing their application.

There are some disadvantages of not writing the hints in a query. But of 
course there are disadvantages to do as well ;)

One I can think of is that it can be very hard to define which hint 
should apply where. Especially in complex queries, defining at which 
point exaclty you'd like your hint to work is not a simple matter, 
unless you can just place a comment right at that position.

Say you have a complex query with several joins of the same table. And 
in all but one of those joins postgresql actually chooses the best 
option, but somehow you keep getting some form of join while a nested 
loop would be best. How would you pinpoint just that specific clause, 
while the others remain "unhinted" ?

Your approach seems to be a bit similar to aspect oriented programming 
(in java for instance). You may need a large amount of information about 
the queries and it is likely a "general" regexp with "general" hint will 
not do much good (at least I expect a hinting-system to be only useable 
in corner cases and very specific points in a query).

By the way, wouldn't it be possible if the planner learned from a query 
execution, so it would know if a choice for a specific plan or estimate 
was actually correct or not for future reference? Or is that in the line 
of DB2's complexity and a very hard problem and/or would it add too much 
overhead?

Best regards,

Arjen