pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: Pavan Deolasee <pavan.deolasee@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: I/O on select count(*)
Date: Thu, 15 May 2008 10:41:06 -0400
Message-ID: <20080515144106.GA8221@alvh.no-ip.org> (raw)
In-Reply-To: <Pine.GSO.4.64.0805142254080.10990@westnet.com>
References: <920841.71690.qm@web44808.mail.sp1.yahoo.com>
	<482B1D5F.EE98.0025.0@wicourts.gov>
	<Pine.GSO.4.64.0805142133250.27290@westnet.com>
	<20080515020553.GN9838@alvh.no-ip.org>
	<Pine.GSO.4.64.0805142211300.5617@westnet.com>
	<2e78013d0805141940x50c3f51u439019e8e592959@mail.gmail.com>
	<Pine.GSO.4.64.0805142254080.10990@westnet.com>

Greg Smith escribió:
> 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 problem is that the bgwriter does not understand about the content
of the pages it is writing -- they're opaque pages for all it knows.  So
it cannot touch the hint bits.

I agree with Pavan that it's likely that setting hint bits in batches
instead of just for the tuple being examined is a benefit.  However,
it's perhaps not so good to be doing it in a foreground process, because
you're imposing extra cost to the client queries which we want to be as
fast as possible.  Perhaps the thing to do is have a "database-local
bgwriter" which would scan pages and do this kind of change ...
a different kind of process to be launched by autovacuum perhaps.

If we had the bitmask in a separate map fork, this could be cheap.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.



view thread (54+ messages)  latest in thread

Message-ID: <20080515144106.GA8221@alvh.no-ip.org>
Permalink:  ../20080515144106.GA8221@alvh.no-ip.org/
Also on:    postgresql.org/message-id/20080515144106.GA8221@alvh.no-ip.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: alvherre@commandprompt.com, gsmith@gregsmith.com, pavan.deolasee@gmail.com
  Subject: Re: I/O on select count(*)
  In-Reply-To: <20080515144106.GA8221@alvh.no-ip.org>
  References: <920841.71690.qm@web44808.mail.sp1.yahoo.com> <482B1D5F.EE98.0025.0@wicourts.gov> <Pine.GSO.4.64.0805142133250.27290@westnet.com> <20080515020553.GN9838@alvh.no-ip.org> <Pine.GSO.4.64.0805142211300.5617@westnet.com> <2e78013d0805141940x50c3f51u439019e8e592959@mail.gmail.com> <Pine.GSO.4.64.0805142254080.10990@westnet.com> <20080515144106.GA8221@alvh.no-ip.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox