On 20.02.2025 21:21, Tom Lane wrote:
Teodor Sigaev <teo...@sigaev.ru> writes:
I'd like to suggest to improve var_eq_non_const() by using knowledge of MCV and
estimate the selectivity as quadratic mean of non-null fraction divided by
number of distinct values (as it was before) and set of MCV selectivities.
What's the statistical interpretation of this calculation (that is,
the average MCV selectivity)? Maybe it's better, but without any
context it seems like a pretty random thing to do. In particular,
it seems like this could give radically different answers depending
on how many MCVs we chose to store, and I'm not sure we could argue
that the result gets more accurate with more MCVs stored.
regards, tom lane
Hi,
The arithmetic mean is not exactly the same as the root mean square
approach implemented by Teodor. The key difference is that the root mean
square is more influenced by the largest values in the distribution. The
further the data deviates from a uniform distribution, the less
representative a simple arithmetic mean becomes.
Theodor's idea seems quite useful to me because it ensures that
selectivity is now influenced by multiple significant values from the
MCV list, rather than just the single most frequent one. This should
lead to a more accurate selectivity estimate, better reflecting the
actual data distribution.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
