Re: Hints proposal

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux