Andy Dale wrote:
Hi,
I turned on the stats_row_level in the postgresql.conf file and now the the
calls to the stats functions work. I want to get the inserted, updated,
and
deleted numbers on a given database, so i have written a query to do so:
SELECT
sum(pg_stat_get_tuples_inserted(c.oid)) AS inserted,
sum(pg_stat_get_tuples_updated(c.oid)) AS updated,
sum(pg_stat_get_tuples_deleted(c.oid)) AS deleted
FROM
pg_class c, information_schema.tables i
WHERE
i.table_catalog = 'testdb' AND
i.table_schema= 'public' AND
i.table_name = c.relname
I had to use the information schema as i could not figure out a way to
fetch
the tables of a particular database using only pg_* tables.
It's the pg_class table you'll want to start with. If you start psql
with -E and then do \dt you'll see the queries it uses.
> What i am
really now concerned is reliability, is it possible that the stats can be
incorrect ? and are they never reset ?. Also does using row level stats
have a serious effect on the performance ?
Well, I'm not sure about incorrect. AFAIK the stats gatherer is "lossy",
so there's not a 100% guarantee that every read/write is measured.
Performance shouldn't be an issue unless you're already pushing the
limits of your hardware.
--
Richard Huxton
Archonet Ltd
