Re: Hints proposal

On Thu, 2006-10-12 at 17:28 -0400, Tom Lane wrote:
> [ trying once again to push this thread over to -hackers where it belongs ]
>
> Arjen van der Meijden <acmmailing(at)tweakers(dot)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.
>

No, I don't have any idea, except that it would be less push-back than
changing a language that's embedded in client code. Also, I see no
reason to think that a hint would not be obsolete upon a new release
anyway.

> 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

"Little better" is all I was going for. I was just making the
observation that we can separate two concepts:
(1) Embedding code in the client's queries, which I see as very
undesirable and unnecessary
(2) Providing very specific hints

which at least gives us a place to talk about the debate more
reasonably.

> that really fixes is not having the hints directly embedded into
> client-side code. It's still wrong at the conceptual level.
>

I won't disagree with that. I will just say it's no more wrong than
applying the same concept in addition to embedding the hints in client
queries.

> 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.
>

Agreed.

Regards,
Jeff Davis