Re: Slow count(*) again...

On 13/10/2010 12:38 AM, Jesper Krogh wrote:

> If some clever postgres hacker could teach postgres to allocate blocks
> using posix_fallocate in quite large batches, say .. something like:
> fallocate(min(current_relation_size *0.1,1073741824))

There doesn't seem to be any use of posix_fallocate in the sources, at 
least according to git grep. The patch that introduced posix_fadvise use 
apparently had posix_fallocate in it, but that use appears to have been 
removed down the track.

It's worth noting that posix_fallocate sucks if your file system doesn't 
intelligent support for it. IIRC it's horrible on ext3, where it can 
take a while to return while it allocates (and IIRC zeroes!) all those 
blocks. This may be part of why it's not used. In past testing with 
posix_fallocate for other tools I've also found rather mixed performance 
results - it can slow things down rather than speed them up, depending 
on the file system in use and all sorts of other factors.

If Pg was to use posix_fallocate, it'd probably need control over it on 
a per-tablespace basis.

-- 
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/