Re: Hints (was Poor performance using CTE)

On Fri, Nov 23, 2012 at 3:05 AM, Cédric Villemain
<cedric(at)2ndquadrant(dot)com> wrote:
> Le mercredi 21 novembre 2012 17:34:02, Craig James a écrit :
>> On Wed, Nov 21, 2012 at 5:42 AM, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:
>> > It's a tough problem. Disguising and not documenting the available
>> > optimizer hints leads to more reports on where the optimizer should
>> > be smarter, and has spurred optimizer improvements. ...
>> > Regarding the above-mentioned benefits we would stand to lose by
>> > having clear and documented hints, perhaps we could occasionally
>> > solicit input on where people are finding hints useful to get ideas
>> > on where we might want to improve the optimizer. As far as worrying
>> > about people using hints to force a plan which is sub-optimal --
>> > isn't that getting into nanny mode a bit too much?
>>
>> Toward that end, the hint documentation (which is almost always viewed as
>> HTML) could be prefaced by a strong suggestion to post performance
>> questions in this group first, with links to the "subscribe" page and the
>> "how to report performance problems" FAQ. The hint documentation could even
>> be minimalistic; suggest to developers that they should post their
>> problematic queries here before resorting to hints. That would give the
>> experts an opportunity to provide the normal advice. The correct hint
>> syntax would be suggested only when all other avenues failed.
>
> We have hooks in PostgreSQL. We already have at least one extension which is
> using that to change the planner behavior.
>
> We can have a bit more hooks and try to improve the cost estimate, this part
> of the code is known to be built by reports and human estimations, also the
> 9.2 version got heavy modifications in this area.
>
> Let the 'Hints' be inside an extension thus we are able to track them and fix
> the planner/costestimate issues.
>
> I don't see why PostgreSQL needs 'Hints' *in-core*.

Here here! PostgreSQL is well known for its extensibility and this is
the perfect place for hints. That way they can get worked on without
becoming a crutch for every user and forcing the backend developers to
support what may or may not be a good idea syntax wise. After a few
different people have banged some code out to make workable hint
syntaxes for their own use maybe then it will be time to revisit
adding hints to core.