Re: Hints proposal

On Thu, 2006-10-12 at 14:34 -0500, Jim C. Nasby wrote:
> On Thu, Oct 12, 2006 at 09:42:55AM -0700, Jeff Davis wrote:
> > On Thu, 2006-10-12 at 10:14 -0500, Jim C. Nasby wrote:
> > > The syntax these hints is something arbitrary. I'm borrowing Oracle's
> > > idea of embedding hints in comments, but we can use some other method if
> > > desired. Right now I'm more concerned with getting the general idea
> > > across.
> > >
> >
> > Is there any advantage to having the hints in the queries? To me that's
> > asking for trouble with no benefit at all. It would seem to me to be
> > better to have a system catalog that defined hints as something like:
> >
> > "If user A executes a query matching regex R, then coerce (or force) the
> > planner in this way."
> >
> > I'm not suggesting that we do that, but it seems better then embedding
> > the hints in the queries themselves.
>
> My experience is that on the occasions when I want to beat the planner
> into submission, it's usually a pretty complex query that's the issue,
> and that it's unlikely to have more than a handful of them in the
> application. That makes me think a regex facility would just get in the
> way, but perhaps others have much more extensive need of hinting.
>
> I also suspect that writing that regex could become a real bear.
>

Well, writing the regex is just matching criteria to apply the hint. If
you really need a quick fix, you can just write a comment with a query
id number in the query. The benefit there is that when the hint is
obsolete later (as the planner improves, or data changes
characteristics) you drop the hint and the query is planned without
interference. No application changes required.

Also, and perhaps more importantly, let's say you are trying to improve
the performance of an existing application where it's impractical to
change the query text (24/7 app, closed source, etc.). You can still
apply a hint if you're willing to write the regex. Just enable query
logging or some such to capture the query, and copy it verbatim except
for a few parameters which are unknown. Instant regex. If you have to
change the query text to apply the hint, it would be impossible in this
case.

> Having said that... I see no reason why it couldn't work... but the real
> challenge is defining the hints.

Right. The only thing I was trying to solve was the problems associated
with the hint itself embedded in the client code. I view that as a
problem that doesn't need to exist.

I'll leave it to smarter people to either improve the planner or develop
a hinting language. I don't even need hints myself, just offering a
suggestion.

Regards,
Jeff Davis