From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org
Subject: Re: read only transactions
Date: Tue, 12 Oct 2010 17:16:20 -0400
Message-ID: <7011.1286918180@sss.pgh.pa.us> (raw)
In-Reply-To: <87zkuj884g.fsf@cbbrowne.afilias-int.info>
References: <AANLkTikaFMqNPcyAch5t3tL7Sj+HU0eiDaCj_vnFACOz@mail.gmail.com>
<87zkuj884g.fsf@cbbrowne.afilias-int.info>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: read only transactions
In-Reply-To: <7011.1286918180@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox