Re: Index usage vs large repetitions of key

On Sun, 5 May 2002 felix(at)crowfix(dot)com wrote:

> This sequential scan bugged the heck out of me, until I finally
> understood what is going on. Usually records on disk are scattered
> all over, so many per data page. I think the standard is 8K. Suppose
> your table has 10 records per data page. Roughly 1/20 of the records
> will be selected, so it is going to hit about half the data pages. In
> this case, it is better to simply march through all data pages
> sequentially than to read half the data pages randomly, and also hit a
> lot of index pages, also randomly scattered around the disk. It's
> better for the OS to read twice as much sequentially compared to half
> as much randomly. Throw in more random reading for index pages, and
> the sequential scan is a big win.

Thanks for the explanation. So I guess it is a factor of how big each row
is, the percentage of records to be selected and the page size.
The particular queries I am doing would return about %10 of the records
and using 8K pages there would be about 200 rows per page. Now I better
understand why the index is not been used.

> I think there is some way to force an indexed read, but I have
> forgotten what little I knew about that. If there is, you could try
> both ways and compare timings.

Based on this info it may make sense to let it do the sequential scan.

In the coming months the table in question is going to grow 3 to 4 times
it's number of records so at that point the index may make more sense.

Is there a drawback on having the index right now?
I guess it would make the optimizer's work more even though it would
likely not choose the index anyway.