Re: Plan stability versus near-exact ties in cost estimates

On Thu, Apr 19, 2012 at 11:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> A variant idea would be to replace the exact cost comparison with a
> second round of fuzzy cost comparison, but with a much tighter fuzz
> factor, maybe 1e-6 instead of 0.01.

The fuzz factor is a better idea, IMHO. I would like to see that as a
user set parameter.

Jim is right that plan stability is a wide problem, which could be
addressed by setting a higher fuzz factor when plan instability is
observed. Instability normally occurs for queries near a decision
point in the cost models, which can fluctuate as exacts stats change.
For those queries, it would be much better to address the instability
directly via a fuzz factor than to attempt to fiddle with the enable_*
parameters as is now common practice.

Doc entry for the parameter...

plan_choice_factor (float) - plans that vary in cost by less than this
factor are considered equal by the planner, leading to a consistent
choice of the first derived plans even across different types of
hardware. This has a tendency to favour indexed plans when they are
available. This is designed to address problems related to plan
stability and may not produce desired results if used as a
hinting/tweaking mechanism.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services