[ trying once again to push this thread over to -hackers where it belongs ]
Arjen van der Meijden <acmmailing@tweakers.net> writes:
> 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.
Do you have any idea how much push-back there would be to that? In
practice we'd be bound by backwards-compatibility concerns for the hints
too.
> 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.
The problems that you are seeing all come from the insistence that a
hint should be textually associated with a query. Using a regex is a
little better than putting it right into the query, but the only thing
that really fixes is not having the hints directly embedded into
client-side code. It's still wrong at the conceptual level.
The right way to think about it is to ask why is the planner not picking
the right plan to start with --- is it missing a statistical
correlation, or are its cost parameters wrong for a specific case, or
is it perhaps unable to generate the desired plan at all? (If the
latter, no amount of hinting is going to help.) If it's a statistics or
costing problem, I think the right thing is to try to fix it with hints
at that level. You're much more likely to fix the behavior across a
class of queries than you will be with a hint textually matched to a
specific query.
regards, tom lane
