Hi Craig and Shawn I fully agree with your argumentation. Who's the elephant in the room who is reluctant to introduce explicit hints?
-S. 2014-04-14 17:35 GMT+02:00 Craig James <cja...@emolecules.com>: > Shaun Thomas <stho...@optionshouse.com> wrote: > >> >>> these issues tend to get solved through optimization fences. >>>> Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick. >>>> How are these nothing other than unofficial hints? >>>> >>> Yeah, the cognitive dissonance levels get pretty high around this >>> issue. Some of the same people who argue strenuously against >>> adding hints about what plan should be chosen also argue against >>> having clearly equivalent queries optimize to the same plan because >>> they find the fact that they don't useful for coercing a decent >>> plan sometimes. That amounts to a hint, but obscure and >>> undocumented. (The OP may be wondering what this "OFFSET 0 trick" >>> is, and how he can use it.) >>> >> > +1. I've said this or something like it at least a half-dozen times. > Postgres DOES have hints, they're just obscure, undocumented and hard to > use. If a developer chooses to use them, they become embedded in the app > and forgotten. They're hard to find because there's nothing explicit in the > SQL to look for. You have to know to look for things like "OFFSET" or "SET > ...". Five years down the road when the developer is long gone, who's going > to know why "... OFFSET 0" was put in the code unless the developer made > careful comments? > > >> With explicit, documented hints, one could search for hints of a >>> particular type should the optimizer improve to the point where >>> they are no longer needed. It is harder to do that with subtle >>> differences in syntax choice. Figuring out which CTEs or LIMITs >>> were chosen because they caused optimization barriers rather than >>> for their semantic merit takes some effort. >> >> > Exactly. > > I'll make a bet here. I'll bet that the majority of large Postgres > installations have at least one, probably several, SQL statements that have > been "hinted" in some way, either with CTEs or LIMITs, or by using SET to > disable a particular query type, and that these "hints" are critical to the > system's performance. > > The question is not whether to have hints. The question is how to expose > hints to users. > > Craig > >
