Re: Index Scans become Seq Scans after VACUUM ANALYSE

Tom Lane wrote:

>mlw <markw(at)mohawksoft(dot)com> writes:
>
>>If the DBA notices that there is a problem with a query, he adds an
>>index, he notices that there is no difference, then he notices that
>>PostgreSQL is not using his index. First and foremost he gets mad at
>>PostgreSQL for not using his index. If PostgreSQL decided to use an
>>index which increases execution time, the DBA would delete the
>>index.
>>
>
>I don't buy that argument at all. It might be a unique index that he
>must have in place for data integrity reasons. It might be an index
>that he needs for a *different* query.
>
>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.
>
On this point I fully agree. A cost-estimator helps massively in cases
where there are multiple candidate indices. My only current complaint
is the current coster needs either a little optimisation work, or
perhaps some of it's internals (like random_page_cost) exposed in a
malleable way.

What is valid for one application will not (necessarily) be valid for
another application. OR set of applications. OLTP has entirely
different goals from Data Warehouseing. Throwing out the coster would
be throwing the baby out with the bathwater. The coster is *not* satan.
It may be the root of a few evils though ]:>