Re: PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans

On Sun, 27 Sep 2009, Sam Mason wrote:

> The first run of:
>  select count(*) from benchmark;
> Will cause the "hint" bits to get set and will cause a lot of writing to
> happen.  Subsequent runs will be testing read performance.

You just need to be careful of caching effects here.  Either stop the
database and clear the system caches before doing the second count(*), or
pick a table size that's much larger than total system RAM so it's can't
possibly cache everything.  Otherwise you'll just be reading back from
cached memory instead on the second read (and the first one, too, but
because of hint bits that result doesn't mean much anyway).

Sam's results represent the status quo as I'm used to seeing it:  you
should see about the "wire speed" of the disk when pulling in data this
way, but both hint bits and checkpoints can slow results if you're not
careful to account for them.  I keep meaning to add something just like
this as a second level example on top of dd/bonnie++ on my disk testing
page.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD