Re: db stats vs table stats

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.

I would have thought that for a given period, the change in
pg_stat_database.blks_read would be <= the sum of the changes in
pg_statio_user_tables.heap_blks_read +
pg_statio_user_tables.idx_blks_read +
pg_statio_user_tables.toast_blks_read +
pg_statio_user_tables.tidx_blks_read.

In short, the total would be <= heap + idx + toast + idx for user
tables.

It does not appear that way. The table-level IO stats appear to
be typically 1-2 orders of magnitude larger than the db-level
stats. Can anyone explain that?

TIA.
Ed

Oops, typo: I reversed the inequality. I've corrected it below.

On Friday February 23 2007 2:02 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.
>
> I would have thought that for a given period, the change in
> pg_stat_database.blks_read would be >= the sum of the changes
> in pg_statio_user_tables.heap_blks_read +
> pg_statio_user_tables.idx_blks_read +
> pg_statio_user_tables.toast_blks_read +
> pg_statio_user_tables.tidx_blks_read.
>
> In short, the total would be >= heap + idx + toast + idx for
> user tables.
>
> It does not appear that way. The table-level IO stats appear
> to be typically 1-2 orders of magnitude larger than the
> db-level stats. Can anyone explain that?
>
> TIA.
> Ed

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)