Re: SeqScan costs

On Aug 13, 2008, at 10:45 PM, Andrew Gierth wrote:
> You could likely expose a difference using LIMIT 1 in the subselect,
> but that doesn't tell us anything we didn't already know (which is
> that yes, index scan is much faster than seqscan even for 1-block
> tables, except in the rare case when neither the index page nor the
> table page are in cache, causing the indexscan to take two page
> fetches rather than just one).
>
> Oddly enough, when I try it with LIMIT 1, it _does_ show a significant
> speed difference according to the row position, _but_ the index scan
> is still twice as fast even when fetching only row 1 (which is indeed
> physically first).

So the question is: why?? How can it be cheaper to hit 2 buffers than 1?

Though, unless we can improve the speed of seqscanning an entire page
vs pulling the exact row we need it's probably still a moot point.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828