Hi, hackers!
It seems we have a problem in pg_statio_all_tables view defenition.
According to the documentation and identification fields, this view
must have exact one row per a table.
The view definition contains an x.indexrelid as the last field in its
GROUP BY list:
<...>
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indexrelid
Which is the oid of a TOAST-index.
However it is possible that the TOAST table will have more than one
index. For example, this happens when REINDEX CONCURRENTLY operation
lefts an index in invalid state (indisvalid = false) due to some kind
of a failure. It's often sufficient to interrupt REINDEX CONCURRENTLY
operation right after start.
Such index will cause the second row to appear in a
pg_statio_all_tables view which obvious is unexpected behaviour.
Now we can have several regular indexes and several TOAST-indexes for
the same table. Statistics for the regular and TOAST indexes is to be
calculated the same way so I've decided to use a CTE here.
The proposed view definition follows:
CREATE VIEW pg_statio_all_tables AS
WITH indstat AS (
SELECT
indrelid,
sum(pg_stat_get_blocks_fetched(indexrelid) -
pg_stat_get_blocks_hit(indexrelid))::bigint
AS idx_blks_read,
sum(pg_stat_get_blocks_hit(indexrelid))::bigint
AS idx_blks_hit
FROM
pg_index
GROUP BY indrelid
)
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,
I.idx_blks_read AS idx_blks_read,
I.idx_blks_hit 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,
X.idx_blks_read AS tidx_blks_read,
X.idx_blks_read AS tidx_blks_hit
FROM pg_class C LEFT JOIN
indstat I ON C.oid = I.indrelid LEFT JOIN
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
indstat X ON T.oid = X.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN ('r', 't', 'm');
Reported by Sergey Grinko.
Regards.
--
Andrei Zubkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company