Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitce95c54

Browse files
committed
Fix pg_statio_all_tables view for multiple TOAST indexes.
A TOAST table can normally have only one index, but there are cornercases where it has more; for example, transiently during REINDEXCONCURRENTLY. In such a case, the pg_statio_all_tables view producedmultiple rows for the owning table, one per TOAST index. Refactor theview 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 unwisedue to the difficulty of putting a system view change into backbranches.Andrei Zubkov, tweaked a bit by meDiscussion:https://postgr.es/m/acefef4189706971fc475f912c1afdab1c48d627.camel@moonset.ru
1 parent7dac614 commitce95c54

File tree

3 files changed

+34
-20
lines changed

3 files changed

+34
-20
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 20 additions & 11 deletions
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))::bigintAS idx_blks_read,
732-
sum(pg_stat_get_blocks_hit(I.indexrelid))::bigintAS idx_blks_hit,
730+
I.idx_blks_readAS idx_blks_read,
731+
I.idx_blks_hitAS 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_readAS tidx_blks_read,
736+
X.idx_blks_hitAS tidx_blks_hit
739737
FROM pg_class CLEFT JOIN
740-
pg_index IONC.oid=I.indrelidLEFT JOIN
741-
pg_class TONC.reltoastrelid=T.oidLEFT JOIN
742-
pg_index XONT.oid=X.indrelid
738+
pg_class TONC.reltoastrelid=T.oid
743739
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
744-
WHEREC.relkindIN ('r','t','m')
745-
GROUP BYC.oid,N.nspname,C.relname,T.oid,X.indexrelid;
740+
LEFT JOIN LATERAL (
741+
SELECTsum(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_indexWHERE indrelid=C.oid ) ION true
747+
LEFT JOIN LATERAL (
748+
SELECTsum(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_indexWHERE indrelid=T.oid ) XON true
754+
WHEREC.relkindIN ('r','t','m');
746755

747756
CREATEVIEWpg_statio_sys_tablesAS
748757
SELECT*FROM pg_statio_all_tables

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202203221
56+
#defineCATALOG_VERSION_NO202203241
5757

5858
#endif

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

Lines changed: 13 additions & 8 deletions
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 ASidx_blks_read,
2273-
(sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint ASidx_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)
[8]ページ先頭
©2009-2025 Movatter.jp