Re: Hints (was Poor performance using CTE)

On 22.11.2012 02:53, Jeff Janes wrote:
>> That gives the planner the information it needs to choose the right plan on
>> its own. That kind of hints would be much less implementation specific and
>> much more likely to still be useful, or at least not outright
>> counter-productive, in a future version with a smarter planner.
>
> When I run into unexpectedly poor performance, I have an intuitive
> enough feel for my own data that I know what plan it ought to be
> using.  Figuring out why it is not using it is very hard.  For one
> thing, EXPLAIN tells you about the "winning" plan, but there is no
> visibility into what ought to be the winning plan but isn't, so no way
> to see why it isn't.    So you first have to use our existing non-hint
> hints (enable_*, doing weird things with cost_*, CTE stuff) to trick
> it into using the plan I want it to use, before I can figure out why
> it isn't using it, before I could figure out what hints of the style
> you are suggesting to supply to get it to use it.

I'm sure that happens too, but my gut feeling is that more often the
EXPLAIN ANALYZE output reveals a bad estimate somewhere in the plan, and
the planner chooses a bad plan based on the bad estimate. If you hint
the planner by giving a better estimate for where the estimator got it
wrong, the planner will choose the desired plan.

- Heikki