From ce95c543763b6fade641a67fa0c70649d8527243 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 24 Mar 2022 16:33:13 -0400 Subject: Fix pg_statio_all_tables view for multiple TOAST indexes. A TOAST table can normally have only one index, but there are corner cases where it has more; for example, transiently during REINDEX CONCURRENTLY. In such a case, the pg_statio_all_tables view produced multiple rows for the owning table, one per TOAST index. Refactor the view to avoid that, instead summing the stats across all the indexes, as we do for regular table indexes. While this has been wrong for a long time, back-patching seems unwise due to the difficulty of putting a system view change into back branches. Andrei Zubkov, tweaked a bit by me Discussion: https://postgr.es/m/acefef4189706971fc475f912c1afdab1c48d627.camel@moonset.ru --- src/backend/catalog/system_views.sql | 31 ++++++++++++++++++++----------- 1 file changed, 20 insertions(+), 11 deletions(-) (limited to 'src/backend/catalog/system_views.sql') diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 9ac8e9a299..9570a53e7b 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -727,22 +727,31 @@ CREATE VIEW pg_statio_all_tables AS 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, + 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, - pg_stat_get_blocks_fetched(X.indexrelid) - - pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_read, - pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_hit + X.idx_blks_read AS tidx_blks_read, + X.idx_blks_hit 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 + pg_class T ON C.reltoastrelid = T.oid 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.indexrelid; + LEFT JOIN LATERAL ( + SELECT 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 WHERE indrelid = C.oid ) I ON true + LEFT JOIN LATERAL ( + SELECT 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 WHERE indrelid = T.oid ) X ON true + WHERE C.relkind IN ('r', 't', 'm'); CREATE VIEW pg_statio_sys_tables AS SELECT * FROM pg_statio_all_tables -- cgit v1.2.1