Re: Index Scans become Seq Scans after VACUUM ANALYSE - Mailing list pgsql-hackers

From Michael Loftis
Subject Re: Index Scans become Seq Scans after VACUUM ANALYSE
Date
Msg-id 3CC799AF.9020904@wgops.com
Whole thread Raw
In response to Re: Index Scans become Seq Scans after VACUUM ANALYSE  (Curt Sampson <cjs@cynic.net>)
Responses Re: Index Scans become Seq Scans after VACUUM ANALYSE  (Curt Sampson <cjs@cynic.net>)
Re: Index Scans become Seq Scans after VACUUM ANALYSE  (Bruce Momjian <pgman@candle.pha.pa.us>)
List pgsql-hackers
A Block-sized read will not be rboken up.  But if you're reading ina size bigger than the underlying systems block
sizesthen it can get 
 
broken up.

So yes a sequential read will get broken up.  A single read request for 
a block may or may not get broken up.  If you're freading with set block 
sizes you'll see the set sizes of blocks come through, but what the 
underlyign OS does is undefined, same for writing.  If the underlying 
block size is 8KB and you dump 4MB down on it, the OS may (and in many 
cases does) decide to write part of it, do a read ona  nearby sector, 
then write the rest.  This happens when doing long writes that end up 
spanning block groups because the inodes must be allocated.  

So the write WILL get broken up.  Reads are under the same gun.  IT all 
depends on how big.  To the application you may or may not see this 
(probably not, unless you're set non-blocking, because the kernel will 
just sleep you until your data is ready).  Further large read requests 
can of course be re-ordered by hardware.  Tagged Command Queueing on 
SCSI drives and RAIDs.  The ICP Vortex cards I use ina  number of 
systems have 64MB on-board cache.  They quite happily, and often 
re-order reads and writes when queueing them to keep things moving as 
fast as possible (Intel didn't buy them for their cards, they use the 
i960 as it is, Intel swiped them for their IP rights).  The OS also tags 
commands it fires to the ICP, which can be re-ordered on block-sized chunks.

Curt Sampson wrote:

>On Wed, 24 Apr 2002, mlw wrote:
>
>>I am not arguing about whether or not they do it, I am saying it is
>>not always possible. I/O requests do not remain in queue waiting for
>>reordering indefinitely.
>>
>
>It doesn't matter. When they go out to the disk they go out in
>order. On every Unix-based OS I know of, and Novell Netware, if
>you submit a single read request for consecutive blocks, those
>blocks *will* be read sequentially, no matter what the system load.
>
>So to get back to the original arugment:
>
>>>>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.
>>>>
>
>No, it's very real, because your sequential read will not be broken up.
>
>If you think it will, let me know which operating systems this
>happens on, and how exactly it happens.
>
>cjs
>




pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: md5 passwords and pg_shadow
Next
From: Curt Sampson
Date:
Subject: Re: Index Scans become Seq Scans after VACUUM ANALYSE