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>