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

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Thu, Apr 19, 2012 at 11:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

I can see the case for exposing the fuzz factor for the existing round
of fuzzy cost comparisons (in fact there's been a comment there for
years questioning whether we should do so). Essentially the point of
that would be to let the user trade off planning speed against
resolution, because a larger fuzz factor would result in more plans
getting discarded by add_path as being indistinguishably different in
cost, so it would go faster but you'd probably get a plan that was only
within so many percent of being the best available.

(However, I'd like to see somebody do some experimentation to show
that this actually behaves usefully before we invent Yet Another GUC.
In particular it's not clear whether the error would accumulate
unpleasantly over many levels of joining, if you tried to use a large
fuzz factor.)

If we have a second round of fuzzy comparisons, it would only be to
dodge platform-specific roundoff differences, so I would think that
locking its fuzz factor to 1e-6 or 1e-10 or so would be plenty good
enough.

> 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.

User-visible plan instability (ie, instability on a given installation)
usually arises from changes in the contents of pg_statistic. I am
really dubious that changing the fuzz factor would help that in any
useful way. It's possible I guess, but I'd want to see some
experimental proof before we advertise it as being helpful for that.

regards, tom lane