Re: Plan stability versus near-exact ties in cost estimates - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: Plan stability versus near-exact ties in cost estimates
Date
Msg-id CA+U5nM+iNEzJG33R1qOY-3DOjk1eh40=-wnUvm=KPzM_aTvqUA@mail.gmail.com
Whole thread Raw
In response to Plan stability versus near-exact ties in cost estimates  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Plan stability versus near-exact ties in cost estimates
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Guillaume Lelarge
Date:
Subject: RANGE type, and its subtype parameter
Next
From: "Albe Laurenz"
Date:
Subject: Re: Plan stability versus near-exact ties in cost estimates