Transactions, stats and analyze (oh-my)

Started by Steve Crawfordabout 10 years ago2 messagesgeneral
Jump to latest
#1Steve Crawford
scrawford@pinpointresearch.com

We have certain processes that import data then process and distribute the
data. Since the processing looks primarily, but not exclusively, at the new
records an ANALYZE prior to processing yields better plans. Although the
table changes will trigger autovacuum to analyze the table this happens too
late to be of use for the subsequent processing so we run an explicit
analyze (which, when it collides with autovacuum, triggers a warning in the
logs).

I would like to roll a number of steps into a transaction. What happens to
the stats data generated by ANALYZE if the transaction is rolled back?

This message says the stats are reverted:
http://postgresql.nabble.com/Analyze-during-a-transaction-td5775069.html

This implies they are not:
/messages/by-id/E1XjbTw-0002f9-Ri@gemulon.postgresql.org

Cheers,
Steve

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Steve Crawford (#1)
Re: Transactions, stats and analyze (oh-my)

Steve Crawford <scrawford@pinpointresearch.com> writes:

I would like to roll a number of steps into a transaction. What happens to
the stats data generated by ANALYZE if the transaction is rolled back?

This message says the stats are reverted:
http://postgresql.nabble.com/Analyze-during-a-transaction-td5775069.html

That's correct. They're just rows in a table, same as any other database
data.

This implies they are not:
/messages/by-id/E1XjbTw-0002f9-Ri@gemulon.postgresql.org

That's about data in the pg_class row, which is a different thing from
the pg_statistic data that you're worried about.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general