diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2022-03-24 16:33:13 -0400 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2022-03-24 16:33:13 -0400 |
commit | ce95c543763b6fade641a67fa0c70649d8527243 (patch) | |
tree | be93c9329bc90681ad7936fce55c16b4f507489e /src/backend | |
parent | 7dac61402e34c6d41d5d11cdc4c6a55f91e24026 (diff) | |
download | postgresql-ce95c543763b6fade641a67fa0c70649d8527243.tar.gz |
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
Diffstat (limited to 'src/backend')
-rw-r--r-- | src/backend/catalog/system_views.sql | 31 |
1 files changed, 20 insertions, 11 deletions
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 |