Re: transaction_read_only effects on performance, actual meaning (was: How to reduce impact of a query)

Howard Cole wrote:

> You'll be pleased to know that changing the transaction to read only
> (SET TRANSACTION READ ONLY)
> as you suggested seemed to prevent the server from slowing to a halt.

Actually, I'm mostly surprised by that. I primarily suggested issuing
the command to ensure that if your transaction was doing UPDATes or
similar via triggers or function side-effects you weren't aware of, the
transaction would fail and help you pin-point the problem area.

I'm quite curious about why setting the transaction to readonly helped
its performance. Could it be to do with setting hint bits or something
along those lines, perhaps? Anyone?

> Is it not possible that the query optimisation process should determine
> that the query should be read only without
> explicitly stating this?

I don't think it can. You can issue a series of statements that make no
changes to the database, followed by an UPDATE/INSERT/DELETE/ALTER
TABLE/whatever. You could also issue a SELECT that invokes a function
(directly or via a rule) that modifies the database.

The database doesn't know what statements you will issue next.

That said, I thought the transaction_read_only flag had to be set before
any operations were issued, but it seems to be permitted even after
queries have been issued. In fact, it seems to be permitted after DML
has been issued in a transaction, which really confuses me. Is
transaction_read_only not actually applied to the transaction as a whole
like, say, transaction_isolation, but rather on a statement-by-statement
basis? If so, is it not somewhat misleadingly named?

I would not have expected the following to work:

CREATE TABLE a ( i serial primary key, j text );

BEGIN;

INSERT INTO a (j) VALUES ('oats'), ('wheat'), ('beans');

SET transaction_read_only = 1;

SELECT * FROM a;

COMMIT;

but it does.

--
Craig Ringer