Hi!
It appears that definition of pg_statio_all_tables has bug.
CREATE VIEW pg_statio_all_tables AS
SELECT
C.oid AS relid,
N.nspname AS schemaname,
C.relname AS relname,
pg_stat_get_blocks_fetched(C.oid) -
pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
sum(pg_stat_get_blocks_fetched(I.indexrelid) -
pg_stat_get_blocks_hit(I.indexrelid))::bigint AS
idx_blks_read,
sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
pg_stat_get_blocks_fetched(T.oid) -
pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
sum(pg_stat_get_blocks_fetched(X.indexrelid) -
pg_stat_get_blocks_hit(X.indexrelid))::bigint AS
tidx_blks_read,
sum(pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_hit
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid LEFT JOIN
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
pg_index X ON T.oid = X.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indrelid;
Among all the joined tables, only "pg_index I" is expected to have
multiple rows associated with single relation. But we do sum() for
toast index "pg_index X" as well. As the result, we multiply
statistics for toast index by the number of relation indexes. This is
obviously wrong.
Attached patch fixes the view definition to count toast index statistics once.
As a bugfix, I think this should be backpatched. But this patch
requires catalog change. Were similar cases there before? If so,
how did we resolve them?
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company