Re: I/O on select count(*)

[Greg Smith <gsmith(at)gregsmith(dot)com>]

On Thu, 15 May 2008, Pavan Deolasee wrote:

> I had suggested in the past that whenever we set hint bits for a tuple, 
> we should check all other tuples in the page and set their hint bits too 
> to avoid multiple writes of the same page. I guess the idea got rejected 
> because of lack of benchmarks to prove the benefit.

From glancing at http://www.postgresql.org/docs/faqs.TODO.html I got the 
impression the idea was to have the background writer get involved to help 
with this particular situation.  The way things are setup right now, I 
would guess it's impractical for an individual client to be forced to wait 
for all the tuples in a block to be checked just because it ran into one 
tuple that needed its hint bits refreshed.

If the pages that had any hint bit updates since they were read/created 
were made easy to identify (maybe they already are), the writer could do 
the kind of scan you suggest anytime it was about to evict that page. 
That wouldn't be in the client's critical path and it would maximize the 
possible improvement here.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD