To: pgsql-performance@postgresql.org
Subject: Re: read only transactions 
In-reply-to: <87zkuj884g.fsf@cbbrowne.afilias-int.info> 
References: <AANLkTikaFMqNPcyAch5t3tL7Sj+HU0eiDaCj_vnFACOz@mail.gmail.com>
	<87zkuj884g.fsf@cbbrowne.afilias-int.info>
Comments: In-reply-to Chris Browne <cbbrowne@acm.org>
	message dated "Tue, 12 Oct 2010 16:32:31 -0400"
Date: Tue, 12 Oct 2010 17:16:20 -0400
Message-ID: <7011.1286918180@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Chris Browne <cbbrowne@acm.org> writes:
> jnelson+pgsql@jamponi.net (Jon Nelson) writes:
>> Are there any performance implications (benefits) to executing queries
>> in a transaction where
>> SET TRANSACTION READ ONLY;
>> has been executed?

> Directly?  No.

> Indirectly, well, a *leetle* bit...

> Transactions done READ ONLY do not generate actual XIDs, which reduces
> the amount of XID generation (pretty tautological!), which reduces the
> need to do VACUUM to protect against XID wraparound.

You're right that a read-only transaction doesn't generate an XID.
But that is not a function of whether you do SET TRANSACTION READ ONLY;
it's a function of refraining from attempting any database changes.
The SET might be useful for clarifying and enforcing your intent, but
it's not a performance boost to use it, versus just doing the read-only
transaction without it.

Also, I believe that SET TRANSACTION READ ONLY isn't a "hard" read only
restriction anyway --- it'll still allow writes to temp tables for
example, which will cause assignment of an XID.

			regards, tom lane