Re: Index Scans become Seq Scans after VACUUM ANALYSE

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.

OK, yes, sequential scan _can_ be as slow as index scan, but sometimes
it is faster.  Can you provide reasoning why index scan should be
preferred, other than the admin created it, which I already addressed?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026