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
