Skip to content

Commit ce95c54

Browse files
tglsfdctglsfdc
committed
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
1 parent 7dac614 commit ce95c54

File tree

3 files changed

+34
-20
lines changed

3 files changed

+34
-20
lines changed

‎src/backend/catalog/system_views.sql

+20-11
Original file line numberDiff line numberDiff line change
@@ -727,22 +727,31 @@ CREATE VIEW pg_statio_all_tables AS
727727
pg_stat_get_blocks_fetched(C.oid) -
728728
pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
729729
pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
730-
sum(pg_stat_get_blocks_fetched(I.indexrelid) -
731-
pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
732-
sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
730+
I.idx_blks_read AS idx_blks_read,
731+
I.idx_blks_hit AS idx_blks_hit,
733732
pg_stat_get_blocks_fetched(T.oid) -
734733
pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
735734
pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
736-
pg_stat_get_blocks_fetched(X.indexrelid) -
737-
pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_read,
738-
pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_hit
735+
X.idx_blks_read AS tidx_blks_read,
736+
X.idx_blks_hit AS tidx_blks_hit
739737
FROM pg_class C LEFT JOIN
740-
pg_index I ON C.oid = I.indrelid LEFT JOIN
741-
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
742-
pg_index X ON T.oid = X.indrelid
738+
pg_class T ON C.reltoastrelid = T.oid
743739
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
744-
WHERE C.relkind IN ('r', 't', 'm')
745-
GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indexrelid;
740+
LEFT JOIN LATERAL (
741+
SELECT sum(pg_stat_get_blocks_fetched(indexrelid) -
742+
pg_stat_get_blocks_hit(indexrelid))::bigint
743+
AS idx_blks_read,
744+
sum(pg_stat_get_blocks_hit(indexrelid))::bigint
745+
AS idx_blks_hit
746+
FROM pg_index WHERE indrelid = C.oid ) I ON true
747+
LEFT JOIN LATERAL (
748+
SELECT sum(pg_stat_get_blocks_fetched(indexrelid) -
749+
pg_stat_get_blocks_hit(indexrelid))::bigint
750+
AS idx_blks_read,
751+
sum(pg_stat_get_blocks_hit(indexrelid))::bigint
752+
AS idx_blks_hit
753+
FROM pg_index WHERE indrelid = T.oid ) X ON true
754+
WHERE C.relkind IN ('r', 't', 'm');
746755

747756
CREATE VIEW pg_statio_sys_tables AS
748757
SELECT * FROM pg_statio_all_tables

‎src/include/catalog/catversion.h

+1-1
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202203221
56+
#define CATALOG_VERSION_NO 202203241
5757

5858
#endif

‎src/test/regress/expected/rules.out

+13-8
Original file line numberDiff line numberDiff line change
@@ -2269,19 +2269,24 @@ pg_statio_all_tables| SELECT c.oid AS relid,
22692269
c.relname,
22702270
(pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read,
22712271
pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,
2272-
(sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read,
2273-
(sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit,
2272+
i.idx_blks_read,
2273+
i.idx_blks_hit,
22742274
(pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read,
22752275
pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
2276-
(pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid)) AS tidx_blks_read,
2277-
pg_stat_get_blocks_hit(x.indexrelid) AS tidx_blks_hit
2276+
x.idx_blks_read AS tidx_blks_read,
2277+
x.idx_blks_hit AS tidx_blks_hit
22782278
FROM ((((pg_class c
2279-
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
22802279
LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
2281-
LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
22822280
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2283-
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
2284-
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indexrelid;
2281+
LEFT JOIN LATERAL ( SELECT (sum((pg_stat_get_blocks_fetched(pg_index.indexrelid) - pg_stat_get_blocks_hit(pg_index.indexrelid))))::bigint AS idx_blks_read,
2282+
(sum(pg_stat_get_blocks_hit(pg_index.indexrelid)))::bigint AS idx_blks_hit
2283+
FROM pg_index
2284+
WHERE (pg_index.indrelid = c.oid)) i ON (true))
2285+
LEFT JOIN LATERAL ( SELECT (sum((pg_stat_get_blocks_fetched(pg_index.indexrelid) - pg_stat_get_blocks_hit(pg_index.indexrelid))))::bigint AS idx_blks_read,
2286+
(sum(pg_stat_get_blocks_hit(pg_index.indexrelid)))::bigint AS idx_blks_hit
2287+
FROM pg_index
2288+
WHERE (pg_index.indrelid = t.oid)) x ON (true))
2289+
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
22852290
pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
22862291
pg_statio_all_indexes.indexrelid,
22872292
pg_statio_all_indexes.schemaname,

0 commit comments

Comments
 (0)