From: Chris Browne <cbbrowne@acm.org>
Subject: Re: read only transactions
Date: Tue, 12 Oct 2010 16:32:31 -0400
Organization: cbbrowne Computing Inc
Lines: 35
Message-ID: <87zkuj884g.fsf@cbbrowne.afilias-int.info>
References: <AANLkTikaFMqNPcyAch5t3tL7Sj+HU0eiDaCj_vnFACOz@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.2 (gnu/linux)
Cancel-Lock: sha1:5JyZT59N9JgB7Xc0E1BT51ZC46o=
To: pgsql-performance@postgresql.org

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.

  <http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#VACUUM-BASICS>

If you process 50 million transactions, that chews thru 50 million XIDs.

If 45 million of those were processed via READ ONLY transactions, then
the same processing only chews thru 5 million XIDs, meaning that the
XID-relevant vacuums can be done rather less frequently.

This only terribly much matters if:
  a) your database is so large that there are tables on which VACUUM
     would run for a very long time, and

  b) you are chewing through XIDs mighty quickly.

If either condition isn't true, then the indirect effect isn't important
either.  
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
"I'm not switching from slrn.   I'm quite confident that anything that
*needs* to be posted in HTML is fatuous garbage not worth my time."
-- David M. Cook <davecook@home.com>