Matteo Beccati <php@xxxxxxxxxxx> writes: >> I did just think of something we could improve though. The pattern >> selectivity code doesn't make any use of the statistics about "most >> common values". For a constant pattern, we could actually apply the >> pattern test with each common value and derive answers that are exact >> for the portion of the population represented by the most-common-values >> list. > This reminds me what I did in a patch which is currently on hold for the > next release: I've applied a patch to make patternsel() compute the exact result for the MCV list, and use its former heuristics only for the portion of the column population not included in the MCV list. After finishing that work it occurred to me that we could go a step further: if the MCV list accounts for a substantial fraction of the population, we could assume that the MCV list is representative of the whole population, and extrapolate the pattern's selectivity over the MCV list to the whole population instead of using the existing heuristics at all. In a situation like Andreas' example this would win big, although you can certainly imagine cases where it would lose too. Any thoughts about this? What would be a reasonable threshold for "substantial fraction"? It would probably make sense to have different thresholds depending on whether the pattern is left-anchored or not, since the range heuristic only works for left-anchored patterns. regards, tom lane
