On Mon, 5 Mar 2007, Mark Kirkwood wrote: > To add a little to this - forgetting the scan resistant point for the > moment... cranking down shared_buffers to be smaller than the L2 cache > seems to help *any* sequential scan immensely, even on quite modest HW: > > e.g: PIII 1.26Ghz 512Kb L2 cache, 2G ram, > > SELECT count(*) FROM lineitem (which is about 11GB) performance: > > Shared_buffers Elapsed > -------------- ------- > 400MB 101 s > 128KB 74 s > > When I've profiled this activity, I've seen a lot of time spent > searching for/allocating a new buffer for each page being fetched. > Obviously having less of them to search through will help, but having > less than the L2 cache-size worth of 'em seems to help a whole lot!
Could you demonstrate that point by showing us timings for shared_buffers sizes from 512K up to, say, 2 MB? The two numbers you give there might just have to do with managing a large buffer. Thanks, Gavin ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match