Re: question on scan of clustered index

* pwing(at)student(dot)umass(dot)edu (pwing(at)student(dot)umass(dot)edu) wrote:
> I am running three ways: sequential scan, bitmap index scan and index scan.
> The I/O cost for the index scan is 24+ times more than the other two. I do not
> understand why this happens. If I am using a clustered index, it is my
> understanding that there should be no need to retrieve a single page multiple
> times, as tuples are sorted. Am I misunderstanding something?

That does seem kind of bad (24+ times is quite a bit). At the same time
though, you are having to go through the index when you're doing an
index scan whereas you don't with the seq scan, so you're certainly
pulling in more data of the disk.

I'm a bit mystified why you'd think an index scan to pull half the data
from a table is going to be faster than a seq scan anyway though... If
you're trying to speed up the query to pull half the records you might
want to look into partitioned tables instead, though I doubt it'll get
much faster...

Thanks,

Stephen