Re: VACUUM kills Index Scans ?!

On Thu, 15 Mar 2001, Gerald Gutierrez wrote:

> 1) When I create a empty table, and then immediate create an index on a
> column, I can get /index scans/ when searching on that column. But when I
> then run VACUUM, the same search becomes a /sequential scan/. Shouldn't it
> still be an index scan? What's going on here?

> ---------------------------------------------
>
> 2) If I already have some data in a table and I create an index on a
> column, why doesn't subsequent searches then change from sequential scans
> to index scans?

With a small number of rows, a sequence scan will require less
reads/seeks from the filesystem. It's not always correct for the
optimizer to choose to use an index even if it's there.

If you put in lots of rows with distinct values and vacuum analyze (you
want to do that rather than just vacuum) and do a comparison it should
use the index, with only a few rows, the seq scan is probably better.