Re: Hints (was Poor performance using CTE)

From: Cédric Villemain <cedric(at)2ndquadrant(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Craig James <cjames(at)emolecules(dot)com>, Kevin Grittner <kgrittn(at)mail(dot)com>, Craig Ringer <craig(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Claudio Freire <klaussfreire(at)gmail(dot)com>, Merlin Moncure <mmoncure(at)gmail(dot)com>, David Greco <David_Greco(at)harte-hanks(dot)com>
Subject: Re: Hints (was Poor performance using CTE)
Date: 2012-11-23 10:05:52
Message-ID: 201211231105.56380.cedric@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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*.
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Bruce Momjian 2012-11-24 00:21:49 Re: SOLVED - RE: Poor performance using CTE
Previous Message Vitalii Tymchyshyn 2012-11-22 17:37:21 Re: SOLVED - RE: Poor performance using CTE