Re: SeqScan costs

On Wed, Aug 13, 2008 at 07:33:40PM +0100, Andrew Gierth wrote:
> The following message is a courtesy copy of an article
> that has been posted to pgsql.hackers as well.
>
> >>>>> "Decibel!" == Decibel! <decibel(at)decibel(dot)org> writes:
>
> Decibel> Roughly what I get on my MBP (I'm about a factor of 2
> Decibel> slower). This makes me think it's an issue of having to slog
> Decibel> through an entire page one row at a time vs just using the
> Decibel> index. To test this I tested selecting i=200 (remember we
> Decibel> start filling data at the back of the page, so 200 would
> Decibel> actually be the front, and I'm assuming the first value that
> Decibel> would be hit) vs i=1. With seqscans, I saw about a 10%
> Decibel> difference. With index scans the difference was moot, but
> Decibel> also note that now index scans are in-between seqscans in
> Decibel> performance.
>
> The problem is that by looking for a constant row, you're actually
> eliminating the entire effect being tested, because the uncorrelated
> subselect is run ONCE as an initplan, and the entire query time is
> then spent elsewhere. The differences in runtime you're seeing are
> pure noise (the fact that you had to increase the iteration count so
> much should have been a clue here).

Makes sense, and yeah, I was wondering a bit about that. I'll try to
fake it out with offset 0 later on if no one beats me to it; I do still
think we could just be seeing the effect of slogging through 200 tuples
instead of going directly to the one we want.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828