Tom Lane wrote:
> If the table has more than one index available that might be usable
> with a particular query, how does your argument help? It doesn't.
> We still have to trust to statistics and cost estimates. So I intend
> to proceed on the path of improving the estimator, not in the direction
> of throwing it out in favor of rules-of-thumb.
I'm not saying ignore the statistics. The cost analyzer is trying to create a
good query based on the information about the table. Since the statistics are a
summation of table characteristics they will never be 100% accurate.
Complex systems have behaviors which are far more unpredictable in practice,
numbers alone will not predict behavior. Theoretically they can, of course, but
the amount and complexity of the information which would need to be processed
to make a good prediction would be prohibitive. Think about the work being done
in weather prediction. "rules-of-thumb" are quite important. PostgreSQL already
has a number of them, what do you think cpu_tuple_cost and random_page_cost
are?
How about a configuration option? Something like an index_weight ratio.
A setting of 1.0 would tell the optimizer that if the index and the non-index
lookup are the same, it would use the index.
A setting of 2.0 Would tell the optimizer that the index cost would need to be
twice that of the non-index lookup to avoid using the index.
How about that?
