Re: SeqScan costs

>>>>> "Decibel" == Decibel! <decibel(at)decibel(dot)org> writes:

Decibel> OK, ran the test again via this query:

Decibel> explain analyze select (select value from oneblock where id = i)
Decibel> from generate_series(1,1) i, generate_series(1,100000) j;

Decibel> changing 1,1 to 200,200 as needed. I don't see any
Decibel> meaningful differences between 1,1 and 200,200.

Well of course you don't, since it scans all the rows regardless.
(Scalar subselects don't abort after the first row, they only abort if
they find _more_ than one row, and in this example there is only one,
so the whole of "oneblock" is scanned every time.)

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).

--
Andrew (irc:RhodiumToad)