Re: I/O on select count(*)

On May 18, 2008, at 1:28 AM, Greg Smith wrote:
> I just collected all the good internals information included in  
> this thread and popped it onto http://wiki.postgresql.org/wiki/ 
> Hint_Bits where I'll continue to hack away at the text until it's  
> readable.  Thanks to everyone who answered my questions here,  
> that's good progress toward clearing up a very underdocumented area.
>
> I note a couple of potential TODO items not on the official list  
> yet that came up during this discussion:
>
> -Smooth latency spikes when switching commit log pages by  
> preallocating cleared pages before they are needed
>
> -Improve bulk loading by setting "frozen" hint bits for tuple  
> inserts which occur within the same database transaction as the  
> creation of the table into which they're being inserted
>
> Did I miss anything?  I think everything brought up falls either  
> into one of those two or the existing "Consider having the  
> background writer update the transaction status hint bits..." TODO.

Blah, sorry for the double-post, but I just remembered a few things...

Did we completely kill the idea of the bg_writer *or some other  
background process* being responsible for setting all hint-bits on  
dirty pages before they're written out?

Also, Simon and Tom had an idea at PGCon: Don't set hint-bits in the  
back-end if the page isn't already dirty. We'd likely need some  
heuristics on this... based on Luke's comments about improved CLOG  
caching maybe we want to set the bits anyway if the tuples without  
them set are from old transactions (idea being that pulling those  
CLOG pages would be pretty expensive). Or better yet; if we have to  
read a CLOG page off disk, set the bits.

This could still potentially be a big disadvantage for data  
warehouses; though perhaps the way to fix that is recommend a  
backgrounded vacuum after data load.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828