Tom,
> The only real solution, of course, is to acquire cross-column
> statistics, but I don't see that happening in the near future.
Y'know, that's been on the todo list for a while. Surely someone is inspired
for 8.1/8.2? At least for columns which are indexed together?
> As a short-term hack, I am thinking that the "clamp to size of table"
> part of the rule is overly pessimistic, and that we should consider
> something like "clamp to size of table / 10" instead. The justification
> for this is the thought that you aren't going to bother grouping unless
> it actually reduces the data volume. We have used similar rules in the
> past --- for example, before the logic for trying to estimate actual
> group counts was put in, the estimate for the number of output rows
> from an Agg or Group node was just the number of input rows over 10.
Why 10? I'd think we could come up with a slightly less arbitrary number,
based on "At what point does the median possible cost of estmating too low
equal the median possible cost of estimating too high?" This seems
calculable based on the other information available ...
... although perhaps not without a math PhD. Surely there's one in the house?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco