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,
youwould certainly   have seen better performance (assuming you're not CPU-bound--see   below) had you read more,
becauseyou 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.
Inthis case there   are two possibilities:
 
i) You're CPU bound, in which case better disk performance makesno difference anyway, or
ii) You are likely to find the blocks still in memory when youget your next quantum. (Unless you don't have enough
memoryinthe system, in which case, you should fix that before you spendany 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@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC