Re: Planner hints in Postgresql

>
>> > That's precisely what risk estimation was about.
>>
>> Yeah. I would like to see the planner's cost estimates extended to
>> include some sort of uncertainty estimate, whereupon risk-averse people
>> could ask it to prefer low-uncertainty plans over high-uncertainty ones
>> (the plans we typically choose for ORDER BY ... LIMIT queries being great
>> examples of the latter). But it's a long way from wishing that to making
>> it so. Right now it's not even clear (to me anyway) how we'd measure or
>> model such uncertainty.
>>
>
> Well, currently, selectivity estimates based on MCV should be pretty
> low-uncertainty, whereas certainty of other estimates could be modeled as a
> random variable if ANALYZE gathered a few statistical moments (for
> variables that are prone to that kind of statistical analysis).
>
> That alone could improve things considerably, and statistical info could
> be propagated along expressions to make it possible to model uncertainty in
> complex expressions as well.
>
>

That is a sort of solution that I proposed yesterday on the mailing list.
The solution essentially takes lots of samples of the data and then plots
the mean and standard deviation of the independent samples to get the
probability of the histogram selectivity estimate.

The problem is multi faceted (outdated stats, bad guess at distribution of
data, cases Merlin mentioned before (CASE statements, COALESCE statements
etc.). Finding a general solution to this problem shall require a lot of
research and time.

I agree with Tom, we should focus on some of the main problems we have in
that domain and then try to solve them first.

Regards,

Atri

--
Regards,

Atri
*l'apprenant*