Re: Index Scans become Seq Scans after VACUUM ANALYSE

Andrew Sullivan wrote:
> Given the apparent infrequency of docs-consultation, I am
> considerably less sanguine than you are about the correctness of the
> choices many DBAs make. Poking at the planner to make it use an
> index more often strikes me as at least as likely to cause worse
> performance.

I disagree :-)

>
> > I don't think you can solve this with statistics. It is a far more
> > complex problem than that.
>
> Aw, you just need to take more stats courses ;)

You need to move a away form the view that everything calculable and
deterministic and move over to the more chaotic perspective where "more likely
than not" is about the best one can hope for.

The cost based optimizer is just such a system. There are so many things that
can affect the performance of a query that there is no way to adequately model
them. Disk performance, inner/outer tracks, RAID systems, concurrent system
activity, and so on.

Look at the pgbench utility. I can't run that program without a +- 10%
variation from run to run, no mater how many times I run vacuum and checkpoint.

When the estimated cost ranges of the different planner strategies overlap, I
think that is a case where two approximations with indeterminate precision must
be evaluated. In such cases, the variance between the numbers have little or no
absolute relevance to one another. This is where heuristics and a bit of
fuzziness needs to be applied. Favoring an index scan over a sequential scan
would probably generate a better query.