Обсуждение: read only transactions
Are there any performance implications (benefits) to executing queries in a transaction where SET TRANSACTION READ ONLY; has been executed? -- Jon
Jon Nelson <jnelson+pgsql@jamponi.net> wrote: > Are there any performance implications (benefits) to executing > queries in a transaction where > SET TRANSACTION READ ONLY; > has been executed? I don't think it allows much optimization in any current release. It wouldn't be a bad idea to use it where appropriate, though, as future releases might do something with it. If you include this on the BEGIN statement, that will save a round trip. -Kevin
Jon Nelson <jnelson+pgsql@jamponi.net> writes: > Are there any performance implications (benefits) to executing queries > in a transaction where > SET TRANSACTION READ ONLY; > has been executed? No. regards, tom lane
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>
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