Re: Index Scans become Seq Scans after VACUUM ANALYSE

On Thu, 18 Apr 2002, Michael Loftis wrote:

> mlw wrote:
>
> >The supposed advantage of a sequential read over an random read, in
> >an active multitasking system, is a myth. If you are executing one
> >query and the system is doing only that query, you may be right.
> >
> >Execute a number of queries at the same time, the expected benefit
> >of a sequential scan goes out the window. The OS will be fetching
> >blocks, more or less, at random.

On a system that has neither read-ahead nor sorting of I/O requests,
yes. Which systems are you using that provide neither of these
facilities?

> In a multi-tasking system it's always cheaper to fetch less blocks, no
> matter where they are. Because, as you said, it will end up more or
> less random onf a system experiencing a larger number of queries.

Invariably a process or thread will lose its quantum when it submits
an I/O request. (There's nothing left for it to do, since it's waiting
for its data to be read, so there's nothing for it to execute.) It
receives its next quantum when the data are available, and then it may
begin processing the data. There are two possibilities at this point:

a) The process will complete its processing of the current blocks of
data and submit an I/O request. In this case, you would certainly
have seen better performance (assuming you're not CPU-bound--see
below) had you read more, because you would have processed more in
that quantum instead of stopping and waiting for more I/O.

b) In that quantum you cannot complete processing the blocks read
because you don't have any more CPU time left. In this case there
are two possibilities:

i) You're CPU bound, in which case better disk performance makes
no difference anyway, or

ii) You are likely to find the blocks still in memory when you
get your next quantum. (Unless you don't have enough memory in
the system, in which case, you should fix that before you spend
any more time or money on tuning disk performance.)

So basically, it's only cheaper to fetch fewer blocks all the time if
you're doing large amounts of I/O and have relatively little memory. The
latter case is getting more and more rare as time goes on. I'd say at
this point that anybody interested in performance is likely to have at
least 256 MB of memory, which means you're going to need a fairly large
database and a lot of workload before that becomes the problem.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC