Re: Getting query plan alternatives from query planner?

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 <cjames(at)emolecules(dot)com>:

> Shaun Thomas <sthomas(at)optionshouse(dot)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
>
>