Message-ID: <50ACFBBD.30204@vmware.com>
Date: Wed, 21 Nov 2012 18:05:17 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.10) Gecko/20121027 Icedove/10.0.10
MIME-Version: 1.0
To: Kevin Grittner <kgrittn@mail.com>
CC: Craig Ringer <craig@2ndQuadrant.com>, Craig James <cjames@emolecules.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>
In-Reply-To: <20121121134232.68590@gmx.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 21.11.2012 15:42, Kevin Grittner wrote:
> Better, IMV, would be to identify what sorts of hints people actually
> find useful, and use that as the basis for TODO items for optimizer
> improvement as well as inventing clear ways to specify the desired
> coercion. I liked the suggestion that a CTE which didn't need to be
> materialized because of side-effects or multiple references have a
> keyword. Personally, I think that AS MATERIALIZED x (SELECT ...)
> would be preferable to AS x (SELECT ... OFFSET 0) as the syntax to
> specify that.

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 = b.id", 
the user could tell the planner that there are only 10 rows that match 
the "a.id = 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.

- Heikki