Re: Index Scans become Seq Scans after VACUUM ANALYSE

Finally someone writes down whats been itching at my brain for a while.

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.

mlw wrote:

>Bruce Momjian wrote:
>
>>My second point, that index scan is more risky than sequential scan, is
>>outlined above. A sequential scan reads each page once, and uses the
>>file system read-ahead code to prefetch the disk buffers. Index scans
>>are random, and could easily re-read disk pages to plow through a
>>significant portion of the table, and because the reads are random,
>>the file system will not prefetch the rows so the index scan will have
>>to wait for each non-cache-resident row to come in from disk.
>>
>
>It took a bike ride to think about this one. 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.
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>