Re: Hints proposal

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