Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mail@joeconway.com>) id 1TbE8J-0000ly-Ag
	for pgsql-performance@postgresql.org; Wed, 21 Nov 2012 17:30:43 +0000
Received: from wsip-72-214-29-243.sd.sd.cox.net ([72.214.29.243]
	helo=joeconway.com) by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mail@joeconway.com>) id 1TbE8I-00041x-4q
	for pgsql-performance@postgresql.org; Wed, 21 Nov 2012 17:30:42 +0000
Received: from www.joeconway.com (account jconway [192.168.4.40] verified)
	by joeconway.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP-TLS id 12473425; Wed, 21 Nov 2012 09:30:41 -0800
Message-ID: <50AD0FC1.6050802@joeconway.com>
Date: Wed, 21 Nov 2012 09:30:41 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.12) Gecko/20071019 Fedora/1.5.0.12-3.fc6 pango-text
	Thunderbird/1.5.0.12 Mnenhy/0.7.5.666
MIME-Version: 1.0
To: Craig James <cjames@emolecules.com>
CC: Heikki Linnakangas <hlinnakangas@vmware.com>,
	Kevin Grittner <kgrittn@mail.com>,
	Craig Ringer <craig@2ndquadrant.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Claudio Freire <klaussfreire@gmail.com>,
	Merlin Moncure <mmoncure@gmail.com>,
	David Greco <David_Greco@harte-hanks.com>, pgsql-performance@postgresql.org
Subject: Re: Hints (was Poor performance using CTE)
References: <20121121134232.68590@gmx.com> <50ACFBBD.30204@vmware.com>
	<50AD0EA0.7070009@joeconway.com>
	<CAFwQ8reWK_x8JaEAG7_BPCM+NFrEa5uKYfQSyndSSnQtKxMUxQ@mail.gmail.com>
In-Reply-To: 
 <CAFwQ8reWK_x8JaEAG7_BPCM+NFrEa5uKYfQSyndSSnQtKxMUxQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -0.9 (/)
X-Archive-Number: 201211/242
X-Sequence-Number: 48608

On 11/21/2012 09:28 AM, 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.

+1

Joe


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support