Skip site navigation (1) Skip section navigation (2)

Re: I/O on select count(*)

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Matthew Wakeling <matthew(at)flymine(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: I/O on select count(*)
Date: 2008-05-16 18:05:49
Message-ID: Pine.GSO.4.64.0805161118210.4987@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 15 May 2008, Alvaro Herrera wrote:

> Starting a transaction does not write anything to pg_clog.

For Matt and others, some details here are in 
src/backend/access/transam/README:

"pg_clog records the commit status for each transaction that has been 
assigned an XID."

"Transactions and subtransactions are assigned permanent XIDs only when/if 
they first do something that requires one --- typically, 
insert/update/delete a tuple, though there are a few other places that 
need an XID assigned."

After reading the code and that documentation a bit, the part I'm still 
not sure about is whether the CLOG entry is created when the XID is 
assigned and then kept current as the state changes, or whether that isn't 
even in CLOG until the transaction is committed.  It seems like the 
latter, but there's some ambiguity in the wording and too many code paths 
for me to map right now.

From there, it doesn't make its way out to disk until the internal CLOG 
buffers are filled, at which point the least recently used buffer there is 
evicted to permanent storage.

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

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2008-05-16 18:09:46
Subject: Re: very slow left join
Previous:From: BenDate: 2008-05-16 17:56:03
Subject: very slow left join

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group