Re: Index Scans become Seq Scans after VACUUM ANALYSE

At 12:41 PM 4/23/02 -0400, Bruce Momjian wrote:

>This is an interesting point, that an index scan may fit in the cache
>while a sequential scan may not. I can see cases where even a index
>scan of a large percentage of the table may win over an sequential scan.
>Interesting.

Yes and if it fits in the cache the random access costs drop by orders of
magnitude as shown by a recent benchmark someone posted where a Solaris box
cached gigs of data[1].

That's why it might be useful to know what the crossover points for index
scan vs sequential scans for various random page cost values.

e.g. set random page cost to 1 means optimizer will use sequential scan if
it thinks an index scan will return 50% or more rows. set to 0.5 for 75% or
more and so on.

That's probably very simplistic, but basically some idea of what the
optimizer will do given a random page cost could be helpful.

Thanks,
Link.

[1] Mark Pritchard's benchmark where you can see 3rd try onwards random is
actually faster than sequential after caching (TWICE as fast too!).

Machine = jedi (Sun E420, 3gb ram, dual 400s, test on single scsi disk)

Sequential
Bytes Read Time Bytes / Sec
2097152000 65.19 32167675.28
2097152000 65.22 32154114.65
2097152000 65.16 32182561.99
2097152000 65.12 32206105.12
2097152000 64.67 32429463.26
32227984.06 (avg)

Random
Bytes Read Time Bytes / Sec
4194304000 1522.22 2755394.79
4194304000 278.18 15077622.05
4194304000 91.43 45874730.07
4194304000 61.43 68273795.19
4194304000 54.55 76890231.51
41774354.72