Re: Bug in query planer ?

Clifford Wolf <clifford(dot)wolf(at)linbit(dot)com> writes:
>> This would only be helpful if the most-common-values list describes
>> practically all of the column population, which isn't typically the case.

> Not more than it is the case already for the simple 'variable = const'.

No, because while you may not know what the values are that aren't in
the MCV list, you do know that none of them are equal to any of the
values in the MCV list (according to the equality operator used to
develop the list, anyway). This simple bit of logic breaks down as
soon as you are considering f(x) rather than x, because it's entirely
possible that f(x) = f(y) when x != y. Therefore, I don't think it's
valid to infer that the MCV list of the function values would equal
the function computed over the variable's MCV values.

Also, what happens when there's more than one variable used in the
expression? It'll be expensive to compute the expression over the
cartesian product of the MCV lists, and logically dubious anyway
because any such calculation would have to assume that the variable
values are statistically independent, which they likely aren't.

> .. I would do that (auto-generate hundrets of indexes from our slow-query
> log) when there would be some kind of semi-index type which just collects
> statistics on ANALYZE.

Yeah, I've toyed with some such idea too, though I don't think of it as
an index --- just some way to tell ANALYZE that you'd like it to track
statistics for thus-and-such expressions.

regards, tom lane