Re: I/O on select count(*)

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD