From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Kevin Grittner <kgrittn@mail.com>
Cc: Craig Ringer <craig@2ndquadrant.com>
Cc: Craig James <cjames@emolecules.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 11:10:57 +0200
Message-ID: <50ADEC21.9060700@vmware.com> (raw)
In-Reply-To: <CAMkU=1yvc7qV3h57zzcG6eJvJFZN03V-J2nR6pSdJXXdJBB+=A@mail.gmail.com>
References: <20121121134232.68590@gmx.com>
<50ACFBBD.30204@vmware.com>
<CAMkU=1yvc7qV3h57zzcG6eJvJFZN03V-J2nR6pSdJXXdJBB+=A@mail.gmail.com>
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
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: hlinnakangas@vmware.com, jeff.janes@gmail.com, kgrittn@mail.com, craig@2ndquadrant.com, cjames@emolecules.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: <50ADEC21.9060700@vmware.com>
* 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