Date: Wed, 14 May 2008 22:21:45 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
cc: pgsql-performance@postgresql.org
Subject: Re: I/O on select count(*)
In-Reply-To: <20080515020553.GN9838@alvh.no-ip.org>
Message-ID: <Pine.GSO.4.64.0805142211300.5617@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>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Wed, 14 May 2008, Alvaro Herrera wrote:

> If neither of the bits is set, then the transaction is either in 
> progress (which you can check by examining the list of running 
> transactions in shared memory) or your process is the first one to check 
> (in which case, you need to consult pg_clog to know the status, and you 
> can update the hint bits if you find out a permanent state).

So is vacuum helpful here because it will force all that to happen in one 
batch?  To put that another way:  if I've run a manual vacuum, is it true 
that it will have updated all the hint bits to XMIN_COMMITTED for all the 
tuples that were all done when the vacuum started?

> Regarding FAQs, I'm having trouble imagining putting this in the user
> FAQ; I think it belongs into the developer's FAQ.  However, a
> benchmarker is not going to look there.  Maybe we should start "a
> benchmarker's FAQ"?

On the wiki I've started adding a series of things that are 
performance-related FAQs.  There's three of them mixed in the bottom of 
http://wiki.postgresql.org/wiki/Frequently_Asked_Questions right now, 
about slow count(*) and dealing with slow queries.

Here the FAQ would be "Why am I seeing all these writes when I'm just 
doing selects on my table?", and if it's mixed in with a lot of other 
performance related notes people should be able to find it.  The answer 
and suggestions should be simple enough to be useful to a user who just 
noticed this behavior, while perhaps going into developer land for those 
who want to know more about the internals.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD