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

Commita5b5432

Browse files
committed
Add stats_reset to pg_stat_all_{tables,indexes} and related views
It is possible to call pg_stat_reset_single_table_counters() on arelation (index or table) but the reset time was missing from the systemviews showing their statistics.This commit adds the reset time as an attribute of pg_stat_all_tables,pg_stat_all_indexes, and other relations related to them.Bump catalog version.Bump PGSTAT_FILE_FORMAT_ID, as a result of the new field added toPgStat_StatTabEntry.Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>Reviewed-by: Sami Imseih <samimseih@gmail.com>Reviewed-by: Michael Paquier <michael@paquier.xyz>Discussion:https://postgr.es/m/aN8l182jKxEq1h9f@paquier.xyz
1 parentc173aaf commita5b5432

File tree

12 files changed

+117
-33
lines changed

12 files changed

+117
-33
lines changed

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4115,6 +4115,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
41154115
cost-based delays.)
41164116
</para></entry>
41174117
</row>
4118+
4119+
<row>
4120+
<entry role="catalog_table_entry"><para role="column_definition">
4121+
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
4122+
</para>
4123+
<para>
4124+
Time at which these statistics were last reset
4125+
</para></entry>
4126+
</row>
41184127
</tbody>
41194128
</tgroup>
41204129
</table>
@@ -4235,6 +4244,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
42354244
index
42364245
</para></entry>
42374246
</row>
4247+
4248+
<row>
4249+
<entry role="catalog_table_entry"><para role="column_definition">
4250+
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
4251+
</para>
4252+
<para>
4253+
Time at which these statistics were last reset
4254+
</para></entry>
4255+
</row>
42384256
</tbody>
42394257
</tgroup>
42404258
</table>
@@ -4432,6 +4450,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
44324450
Number of buffer hits in this table's TOAST table indexes (if any)
44334451
</para></entry>
44344452
</row>
4453+
4454+
<row>
4455+
<entry role="catalog_table_entry"><para role="column_definition">
4456+
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
4457+
</para>
4458+
<para>
4459+
Time at which these statistics were last reset
4460+
</para></entry>
4461+
</row>
44354462
</tbody>
44364463
</tgroup>
44374464
</table>
@@ -4532,6 +4559,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
45324559
Number of buffer hits in this index
45334560
</para></entry>
45344561
</row>
4562+
4563+
<row>
4564+
<entry role="catalog_table_entry"><para role="column_definition">
4565+
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
4566+
</para>
4567+
<para>
4568+
Time at which these statistics were last reset
4569+
</para></entry>
4570+
</row>
45354571
</tbody>
45364572
</tgroup>
45374573
</table>

‎src/backend/catalog/system_views.sql‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -716,7 +716,8 @@ CREATE VIEW pg_stat_all_tables AS
716716
pg_stat_get_total_vacuum_time(C.oid)AS total_vacuum_time,
717717
pg_stat_get_total_autovacuum_time(C.oid)AS total_autovacuum_time,
718718
pg_stat_get_total_analyze_time(C.oid)AS total_analyze_time,
719-
pg_stat_get_total_autoanalyze_time(C.oid)AS total_autoanalyze_time
719+
pg_stat_get_total_autoanalyze_time(C.oid)AS total_autoanalyze_time,
720+
pg_stat_get_stat_reset_time(C.oid)AS stats_reset
720721
FROM pg_class CLEFT JOIN
721722
pg_index IONC.oid=I.indrelid
722723
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
@@ -778,7 +779,8 @@ CREATE VIEW pg_statio_all_tables AS
778779
pg_stat_get_blocks_hit(T.oid)AS toast_blks_read,
779780
pg_stat_get_blocks_hit(T.oid)AS toast_blks_hit,
780781
X.idx_blks_readAS tidx_blks_read,
781-
X.idx_blks_hitAS tidx_blks_hit
782+
X.idx_blks_hitAS tidx_blks_hit,
783+
pg_stat_get_stat_reset_time(C.oid)AS stats_reset
782784
FROM pg_class CLEFT JOIN
783785
pg_class TONC.reltoastrelid=T.oid
784786
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
@@ -818,7 +820,8 @@ CREATE VIEW pg_stat_all_indexes AS
818820
pg_stat_get_numscans(I.oid)AS idx_scan,
819821
pg_stat_get_lastscan(I.oid)AS last_idx_scan,
820822
pg_stat_get_tuples_returned(I.oid)AS idx_tup_read,
821-
pg_stat_get_tuples_fetched(I.oid)AS idx_tup_fetch
823+
pg_stat_get_tuples_fetched(I.oid)AS idx_tup_fetch,
824+
pg_stat_get_stat_reset_time(I.oid)AS stats_reset
822825
FROM pg_class CJOIN
823826
pg_index XONC.oid=X.indrelidJOIN
824827
pg_class IONI.oid=X.indexrelid
@@ -844,7 +847,8 @@ CREATE VIEW pg_statio_all_indexes AS
844847
I.relnameAS indexrelname,
845848
pg_stat_get_blocks_fetched(I.oid)-
846849
pg_stat_get_blocks_hit(I.oid)AS idx_blks_read,
847-
pg_stat_get_blocks_hit(I.oid)AS idx_blks_hit
850+
pg_stat_get_blocks_hit(I.oid)AS idx_blks_hit,
851+
pg_stat_get_stat_reset_time(I.oid)AS stats_reset
848852
FROM pg_class CJOIN
849853
pg_index XONC.oid=X.indrelidJOIN
850854
pg_class IONI.oid=X.indexrelid

‎src/backend/utils/activity/pgstat.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -313,6 +313,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
313313

314314
.flush_pending_cb=pgstat_relation_flush_cb,
315315
.delete_pending_cb=pgstat_relation_delete_pending_cb,
316+
.reset_timestamp_cb=pgstat_relation_reset_timestamp_cb,
316317
},
317318

318319
[PGSTAT_KIND_FUNCTION]= {

‎src/backend/utils/activity/pgstat_relation.c‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -910,6 +910,12 @@ pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref)
910910
pgstat_unlink_relation(pending->relation);
911911
}
912912

913+
void
914+
pgstat_relation_reset_timestamp_cb(PgStatShared_Common*header,TimestampTzts)
915+
{
916+
((PgStatShared_Relation*)header)->stats.stat_reset_time=ts;
917+
}
918+
913919
/*
914920
* Find or create a PgStat_TableStatus entry for rel. New entry is created and
915921
* initialized if not exists.

‎src/backend/utils/adt/pgstatfuncs.c‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -168,6 +168,9 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time)
168168
/* pg_stat_get_lastscan */
169169
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan)
170170

171+
/* pg_stat_get_stat_reset_time */
172+
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat_reset_time)
173+
171174
Datum
172175
pg_stat_get_function_calls(PG_FUNCTION_ARGS)
173176
{

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202509291
60+
#defineCATALOG_VERSION_NO202510061
6161

6262
#endif

‎src/include/catalog/pg_proc.dat‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5537,6 +5537,10 @@
55375537
proname => 'pg_stat_get_lastscan', provolatile => 's', proparallel => 'r',
55385538
prorettype => 'timestamptz', proargtypes => 'oid',
55395539
prosrc => 'pg_stat_get_lastscan' },
5540+
{ oid => '9127', descr => 'statistics: last reset for a relation',
5541+
proname => 'pg_stat_get_stat_reset_time', provolatile => 's',
5542+
proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
5543+
prosrc => 'pg_stat_get_stat_reset_time' },
55405544
{ oid => '1929', descr => 'statistics: number of tuples read by seqscan',
55415545
proname => 'pg_stat_get_tuples_returned', provolatile => 's',
55425546
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',

‎src/include/pgstat.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -212,7 +212,7 @@ typedef struct PgStat_TableXactStatus
212212
* ------------------------------------------------------------
213213
*/
214214

215-
#definePGSTAT_FILE_FORMAT_ID0x01A5BCB7
215+
#definePGSTAT_FILE_FORMAT_ID0x01A5BCB8
216216

217217
typedefstructPgStat_ArchiverStats
218218
{
@@ -454,6 +454,8 @@ typedef struct PgStat_StatTabEntry
454454
PgStat_Countertotal_autovacuum_time;
455455
PgStat_Countertotal_analyze_time;
456456
PgStat_Countertotal_autoanalyze_time;
457+
458+
TimestampTzstat_reset_time;
457459
}PgStat_StatTabEntry;
458460

459461
/* ------

‎src/include/utils/pgstat_internal.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -716,6 +716,7 @@ extern void PostPrepare_PgStat_Relations(PgStat_SubXactStatus *xact_state);
716716

717717
externboolpgstat_relation_flush_cb(PgStat_EntryRef*entry_ref,boolnowait);
718718
externvoidpgstat_relation_delete_pending_cb(PgStat_EntryRef*entry_ref);
719+
externvoidpgstat_relation_reset_timestamp_cb(PgStatShared_Common*header,TimestampTzts);
719720

720721

721722
/*

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

Lines changed: 24 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1798,7 +1798,8 @@ pg_stat_all_indexes| SELECT c.oid AS relid,
17981798
pg_stat_get_numscans(i.oid) AS idx_scan,
17991799
pg_stat_get_lastscan(i.oid) AS last_idx_scan,
18001800
pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
1801-
pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
1801+
pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch,
1802+
pg_stat_get_stat_reset_time(i.oid) AS stats_reset
18021803
FROM (((pg_class c
18031804
JOIN pg_index x ON ((c.oid = x.indrelid)))
18041805
JOIN pg_class i ON ((i.oid = x.indexrelid)))
@@ -1833,7 +1834,8 @@ pg_stat_all_tables| SELECT c.oid AS relid,
18331834
pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time,
18341835
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
18351836
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
1836-
pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time
1837+
pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time,
1838+
pg_stat_get_stat_reset_time(c.oid) AS stats_reset
18371839
FROM ((pg_class c
18381840
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
18391841
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2200,7 +2202,8 @@ pg_stat_sys_indexes| SELECT relid,
22002202
idx_scan,
22012203
last_idx_scan,
22022204
idx_tup_read,
2203-
idx_tup_fetch
2205+
idx_tup_fetch,
2206+
stats_reset
22042207
FROM pg_stat_all_indexes
22052208
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
22062209
pg_stat_sys_tables| SELECT relid,
@@ -2232,7 +2235,8 @@ pg_stat_sys_tables| SELECT relid,
22322235
total_vacuum_time,
22332236
total_autovacuum_time,
22342237
total_analyze_time,
2235-
total_autoanalyze_time
2238+
total_autoanalyze_time,
2239+
stats_reset
22362240
FROM pg_stat_all_tables
22372241
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
22382242
pg_stat_user_functions| SELECT p.oid AS funcid,
@@ -2252,7 +2256,8 @@ pg_stat_user_indexes| SELECT relid,
22522256
idx_scan,
22532257
last_idx_scan,
22542258
idx_tup_read,
2255-
idx_tup_fetch
2259+
idx_tup_fetch,
2260+
stats_reset
22562261
FROM pg_stat_all_indexes
22572262
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
22582263
pg_stat_user_tables| SELECT relid,
@@ -2284,7 +2289,8 @@ pg_stat_user_tables| SELECT relid,
22842289
total_vacuum_time,
22852290
total_autovacuum_time,
22862291
total_analyze_time,
2287-
total_autoanalyze_time
2292+
total_autoanalyze_time,
2293+
stats_reset
22882294
FROM pg_stat_all_tables
22892295
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
22902296
pg_stat_wal| SELECT wal_records,
@@ -2370,7 +2376,8 @@ pg_statio_all_indexes| SELECT c.oid AS relid,
23702376
c.relname,
23712377
i.relname AS indexrelname,
23722378
(pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
2373-
pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
2379+
pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit,
2380+
pg_stat_get_stat_reset_time(i.oid) AS stats_reset
23742381
FROM (((pg_class c
23752382
JOIN pg_index x ON ((c.oid = x.indrelid)))
23762383
JOIN pg_class i ON ((i.oid = x.indexrelid)))
@@ -2394,7 +2401,8 @@ pg_statio_all_tables| SELECT c.oid AS relid,
23942401
(pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read,
23952402
pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
23962403
x.idx_blks_read AS tidx_blks_read,
2397-
x.idx_blks_hit AS tidx_blks_hit
2404+
x.idx_blks_hit AS tidx_blks_hit,
2405+
pg_stat_get_stat_reset_time(c.oid) AS stats_reset
23982406
FROM ((((pg_class c
23992407
LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
24002408
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2413,7 +2421,8 @@ pg_statio_sys_indexes| SELECT relid,
24132421
relname,
24142422
indexrelname,
24152423
idx_blks_read,
2416-
idx_blks_hit
2424+
idx_blks_hit,
2425+
stats_reset
24172426
FROM pg_statio_all_indexes
24182427
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
24192428
pg_statio_sys_sequences| SELECT relid,
@@ -2433,7 +2442,8 @@ pg_statio_sys_tables| SELECT relid,
24332442
toast_blks_read,
24342443
toast_blks_hit,
24352444
tidx_blks_read,
2436-
tidx_blks_hit
2445+
tidx_blks_hit,
2446+
stats_reset
24372447
FROM pg_statio_all_tables
24382448
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
24392449
pg_statio_user_indexes| SELECT relid,
@@ -2442,7 +2452,8 @@ pg_statio_user_indexes| SELECT relid,
24422452
relname,
24432453
indexrelname,
24442454
idx_blks_read,
2445-
idx_blks_hit
2455+
idx_blks_hit,
2456+
stats_reset
24462457
FROM pg_statio_all_indexes
24472458
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
24482459
pg_statio_user_sequences| SELECT relid,
@@ -2462,7 +2473,8 @@ pg_statio_user_tables| SELECT relid,
24622473
toast_blks_read,
24632474
toast_blks_hit,
24642475
tidx_blks_read,
2465-
tidx_blks_hit
2476+
tidx_blks_hit,
2477+
stats_reset
24662478
FROM pg_statio_all_tables
24672479
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
24682480
pg_stats| SELECT n.nspname AS schemaname,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp