From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Craig James <cjames@emolecules.com>
Cc: Joe Conway <mail@joeconway.com>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: Kevin Grittner <kgrittn@mail.com>
Cc: Craig Ringer <craig@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hints (was Poor performance using CTE)
Date: Thu, 22 Nov 2012 10:18:45 +1300
Message-ID: <50AD4535.7030709@archidevsys.co.nz> (raw)
In-Reply-To: <CAFwQ8reWK_x8JaEAG7_BPCM+NFrEa5uKYfQSyndSSnQtKxMUxQ@mail.gmail.com>
References: <20121121134232.68590@gmx.com>
<50ACFBBD.30204@vmware.com>
<50AD0EA0.7070009@joeconway.com>
<CAFwQ8reWK_x8JaEAG7_BPCM+NFrEa5uKYfQSyndSSnQtKxMUxQ@mail.gmail.com>
On 22/11/12 06:28, Craig James wrote:
>
>
> On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.com>> wrote:
>
> On 11/21/2012 08:05 AM, Heikki Linnakangas wrote:
> > Rather than telling the planner what to do or not to do, I'd
> much rather
> > have hints that give the planner more information about the
> tables and
> > quals involved in the query. A typical source of bad plans is
> when the
> > planner gets its cost estimates wrong. So rather than telling the
> > planner to use a nested loop join for "a INNER JOIN b ON a.id
> <http://a.id; = b.id <http://b.id>";,
> > the user could tell the planner that there are only 10 rows that
> match
> > the "a.id <http://a.id; = b.id <http://b.id>"; qual. 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.
> >
> > You could also attach that kind of hints to tables and columns,
> which
> > would be more portable and nicer than decorating all queries.
>
> I like this idea, but also think that if we have a syntax to allow
> hints, it would be nice to have a simple way to ignore all hints
> (yes, I
> suppose I'm suggesting yet another GUC). That way after sprinkling
> your
> SQL with hints, you could easily periodically (e.g. after a Postgres
> upgrade) test what would happen if the hints were removed.
>
>
> Or a three-way choice: Allow, ignore, or generate an error. That would
> allow developers to identify where hints are being used.
>
> Craig
>
>
> Joe
> --
> Joe Conway
> credativ LLC: http://www.credativ.us
> Linux, PostgreSQL, and general Open Source
> Training, Service, Consulting, & 24x7 Support
>
>
>
Or perhaps hints should have the pg version attached, so that they are
automatically ignored when the pg version changed? Problem may then
become people reluctant to upgrade because their hints relate to a
previous version! Sigh...
Even requiring registration of hints and expiring them after a limited
time period would not work - as people would simply automate the process
of registration & application...
Cheers,
Gavin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, cjames@emolecules.com, mail@joeconway.com, hlinnakangas@vmware.com, kgrittn@mail.com, craig@2ndquadrant.com, tgl@sss.pgh.pa.us, klaussfreire@gmail.com, mmoncure@gmail.com, David_Greco@harte-hanks.com
Subject: Re: Hints (was Poor performance using CTE)
In-Reply-To: <50AD4535.7030709@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox