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
