Re: db stats vs table stats

On Friday February 23 2007 3:06 pm, Ed L. wrote:
> > I've been periodically collecting the stats stored in
> > pg_statio_all_tables and pg_stat_database for ~30 different
> > clusters, and have noticed a curiosity... The table-level IO stats
> > appear to be typically 1-2 orders of magnitude larger than
> > the db-level stats.  Can anyone explain that?

Here's an example of how I'm calculating the deltas. Perhaps
someone can spot an error or mistaken assumption.  In this
case, the deltas are not orders of magnitude out of sync
with each other, but they grew from about 3% out of sync to
45% out of sync in ~35 minutes on a DB with 500 transactions/
second.

drop table s;
create table s as
select now(), blks_read as db_blks_read,
       sum(case when heap_blks_read ISNULL then 0 else heap_blks_read end +
           case when idx_blks_read ISNULL then 0 else idx_blks_read end +
           case when toast_blks_read ISNULL then 0 else toast_blks_read end +
           case when tidx_blks_read ISNULL then 0 else tidx_blks_read end) as table_blks_read
from pg_stat_database sd, pg_database d, pg_class c, pg_statio_all_tables st
where sd.datname = d.datname
  and d.datname = current_database()
  and c.oid = st.relid
group by blks_read;

create or replace view delta_view as
select now() - s.now as delta, blks_read - s.db_blks_read as db_blks_read_delta,
       sum(case when heap_blks_read ISNULL then 0 else heap_blks_read end +
           case when idx_blks_read ISNULL then 0 else idx_blks_read end +
           case when toast_blks_read ISNULL then 0 else toast_blks_read end +
           case when tidx_blks_read ISNULL then 0 else tidx_blks_read end) -
       s.table_blks_read as table_blks_read_delta
from pg_stat_database sd, pg_database d, pg_class c, pg_statio_all_tables st, s
where sd.datname = d.datname
  and d.datname = current_database()
  and c.oid = st.relid
group by blks_read, s.now, db_blks_read, table_blks_read;

select * from delta_view;

      delta      | db_blks_read_delta | table_blks_read_delta
-----------------+--------------------+-----------------------
 00:32:51.007703 |             384243 |                556212
(1 row)