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