DML statistics on a table

Question

We're using PostgreSQL 8.2.

In our application, we heavily use a temporary table (REPORTTEMP) for report generation purpose. All type of DML statements are performed in this table, but UPDATE statement is comparatively very low with INSERTs and DELETEs. So, at any point of time, after completion of the transaction, record count in this table will always be zero.

My question is, how do I find out how many INSERTs, UPDATEs and DELETEs are happening in this table, that is hit count of a table. I require this statistics for further tuning from the performance aspect.

I also read about PostgreSQL's Statistics Collector here http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html but this comes at the expense of additional run-time overhead.

Before enabling this statistcs collector, is there any different/better way in which we can find out this?

Answer 1

Just before dropping the temp table (or before closing the connection), select pg_stats into a table where you store all the information:

INSERT INTO history SELECT * FROM pg_stat_user_tables WHERE relname = 'temp_table';