Message-ID: <50ADEC21.9060700@vmware.com>
Date: Thu, 22 Nov 2012 11:10:57 +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: Jeff Janes <jeff.janes@gmail.com>
CC: Kevin Grittner <kgrittn@mail.com>, 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> <50ACFBBD.30204@vmware.com>
	<CAMkU=1yvc7qV3h57zzcG6eJvJFZN03V-J2nR6pSdJXXdJBB+=A@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1yvc7qV3h57zzcG6eJvJFZN03V-J2nR6pSdJXXdJBB+=A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 22.11.2012 02:53, Jeff Janes wrote:
>> 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.
>
> When I run into unexpectedly poor performance, I have an intuitive
> enough feel for my own data that I know what plan it ought to be
> using.  Figuring out why it is not using it is very hard.  For one
> thing, EXPLAIN tells you about the "winning" plan, but there is no
> visibility into what ought to be the winning plan but isn't, so no way
> to see why it isn't.    So you first have to use our existing non-hint
> hints (enable_*, doing weird things with cost_*, CTE stuff) to trick
> it into using the plan I want it to use, before I can figure out why
> it isn't using it, before I could figure out what hints of the style
> you are suggesting to supply to get it to use it.

I'm sure that happens too, but my gut feeling is that more often the 
EXPLAIN ANALYZE output reveals a bad estimate somewhere in the plan, and 
the planner chooses a bad plan based on the bad estimate. If you hint 
the planner by giving a better estimate for where the estimator got it 
wrong, the planner will choose the desired plan.

- Heikki