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

Commitc037471

Browse files
committed
pgstat: Track time of the last scan of a relation
It can be useful to know when a relation has last been used, e.g., whenevaluating whether an index is still required. It was already possible toinfer the time of the last usage by tracking, e.g.,pg_stat_all_indexes.idx_scan over time. But far from everybody does so.To make it easier to detect the last time a relation has been scanned, trackthat time in each relation's pgstat entry. To minimize overhead a) thetimestamp is updated only when the backend pending stats entry is flushed toshared stats b) the last transaction's stop timestamp is used as thetimestamp.Bumps catalog and stats format versions.Author: Dave Page <dpage@pgadmin.org>Reviewed-by: Andres Freund <andres@anarazel.de>Reviewed-by: Bruce Momjian <bruce@momjian.us>Reviewed-by: Vik Fearing <vik@postgresfriends.org>Discussion:https://postgr.es/m/CA+OCxozrVHNFVEPkweUHMZje+t1tfY816d9MZYc6eZwOOusOaQ@mail.gmail.com
1 parent309b2cf commitc037471

File tree

10 files changed

+356
-2
lines changed

10 files changed

+356
-2
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4385,6 +4385,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
43854385
</para></entry>
43864386
</row>
43874387

4388+
<row>
4389+
<entry role="catalog_table_entry"><para role="column_definition">
4390+
<structfield>last_seq_scan</structfield> <type>timestamptz</type>
4391+
</para>
4392+
<para>
4393+
The time of the last sequential scan on this table, based on the
4394+
most recent transaction stop time
4395+
</para></entry>
4396+
</row>
4397+
43884398
<row>
43894399
<entry role="catalog_table_entry"><para role="column_definition">
43904400
<structfield>seq_tup_read</structfield> <type>bigint</type>
@@ -4403,6 +4413,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
44034413
</para></entry>
44044414
</row>
44054415

4416+
<row>
4417+
<entry role="catalog_table_entry"><para role="column_definition">
4418+
<structfield>last_idx_scan</structfield> <type>timestamptz</type>
4419+
</para>
4420+
<para>
4421+
The time of the last index scan on this table, based on the
4422+
most recent transaction stop time
4423+
</para></entry>
4424+
</row>
4425+
44064426
<row>
44074427
<entry role="catalog_table_entry"><para role="column_definition">
44084428
<structfield>idx_tup_fetch</structfield> <type>bigint</type>
@@ -4654,6 +4674,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
46544674
</para></entry>
46554675
</row>
46564676

4677+
<row>
4678+
<entry role="catalog_table_entry"><para role="column_definition">
4679+
<structfield>last_idx_scan</structfield> <type>timestamptz</type>
4680+
</para>
4681+
<para>
4682+
The time of the last scan on this index, based on the
4683+
most recent transaction stop time
4684+
</para></entry>
4685+
</row>
4686+
46574687
<row>
46584688
<entry role="catalog_table_entry"><para role="column_definition">
46594689
<structfield>idx_tup_read</structfield> <type>bigint</type>

‎src/backend/catalog/system_views.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -656,8 +656,10 @@ CREATE VIEW pg_stat_all_tables AS
656656
N.nspnameAS schemaname,
657657
C.relnameAS relname,
658658
pg_stat_get_numscans(C.oid)AS seq_scan,
659+
pg_stat_get_lastscan(C.oid)AS last_seq_scan,
659660
pg_stat_get_tuples_returned(C.oid)AS seq_tup_read,
660661
sum(pg_stat_get_numscans(I.indexrelid))::bigintAS idx_scan,
662+
max(pg_stat_get_lastscan(I.indexrelid))AS last_idx_scan,
661663
sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint+
662664
pg_stat_get_tuples_fetched(C.oid)AS idx_tup_fetch,
663665
pg_stat_get_tuples_inserted(C.oid)AS n_tup_ins,
@@ -774,6 +776,7 @@ CREATE VIEW pg_stat_all_indexes AS
774776
C.relnameAS relname,
775777
I.relnameAS indexrelname,
776778
pg_stat_get_numscans(I.oid)AS idx_scan,
779+
pg_stat_get_lastscan(I.oid)AS last_idx_scan,
777780
pg_stat_get_tuples_returned(I.oid)AS idx_tup_read,
778781
pg_stat_get_tuples_fetched(I.oid)AS idx_tup_fetch
779782
FROM pg_class CJOIN

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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -789,6 +789,12 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
789789
tabentry=&shtabstats->stats;
790790

791791
tabentry->numscans+=lstats->t_counts.t_numscans;
792+
if (lstats->t_counts.t_numscans)
793+
{
794+
TimestampTzt=GetCurrentTransactionStopTimestamp();
795+
if (t>tabentry->lastscan)
796+
tabentry->lastscan=t;
797+
}
792798
tabentry->tuples_returned+=lstats->t_counts.t_tuples_returned;
793799
tabentry->tuples_fetched+=lstats->t_counts.t_tuples_fetched;
794800
tabentry->tuples_inserted+=lstats->t_counts.t_tuples_inserted;

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,19 @@ pg_stat_get_numscans(PG_FUNCTION_ARGS)
5252
}
5353

5454

55+
Datum
56+
pg_stat_get_lastscan(PG_FUNCTION_ARGS)
57+
{
58+
Oidrelid=PG_GETARG_OID(0);
59+
PgStat_StatTabEntry*tabentry;
60+
61+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)
62+
PG_RETURN_NULL();
63+
else
64+
PG_RETURN_TIMESTAMPTZ(tabentry->lastscan);
65+
}
66+
67+
5568
Datum
5669
pg_stat_get_tuples_returned(PG_FUNCTION_ARGS)
5770
{

‎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_NO202209291
60+
#defineCATALOG_VERSION_NO202210141
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5252,6 +5252,10 @@
52525252
proname => 'pg_stat_get_numscans', provolatile => 's', proparallel => 'r',
52535253
prorettype => 'int8', proargtypes => 'oid',
52545254
prosrc => 'pg_stat_get_numscans' },
5255+
{ oid => '9976', descr => 'statistics: time of the last scan for table/index',
5256+
proname => 'pg_stat_get_lastscan', provolatile => 's', proparallel => 'r',
5257+
prorettype => 'timestamptz', proargtypes => 'oid',
5258+
prosrc => 'pg_stat_get_lastscan' },
52555259
{ oid => '1929', descr => 'statistics: number of tuples read by seqscan',
52565260
proname => 'pg_stat_get_tuples_returned', provolatile => 's',
52575261
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',

‎src/include/pgstat.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -242,7 +242,7 @@ typedef struct PgStat_TableXactStatus
242242
* ------------------------------------------------------------
243243
*/
244244

245-
#definePGSTAT_FILE_FORMAT_ID0x01A5BCA8
245+
#definePGSTAT_FILE_FORMAT_ID0x01A5BCA9
246246

247247
typedefstructPgStat_ArchiverStats
248248
{
@@ -354,6 +354,7 @@ typedef struct PgStat_StatSubEntry
354354
typedefstructPgStat_StatTabEntry
355355
{
356356
PgStat_Counternumscans;
357+
TimestampTzlastscan;
357358

358359
PgStat_Countertuples_returned;
359360
PgStat_Countertuples_fetched;

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1763,6 +1763,7 @@ pg_stat_all_indexes| SELECT c.oid AS relid,
17631763
c.relname,
17641764
i.relname AS indexrelname,
17651765
pg_stat_get_numscans(i.oid) AS idx_scan,
1766+
pg_stat_get_lastscan(i.oid) AS last_idx_scan,
17661767
pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
17671768
pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
17681769
FROM (((pg_class c
@@ -1774,8 +1775,10 @@ pg_stat_all_tables| SELECT c.oid AS relid,
17741775
n.nspname AS schemaname,
17751776
c.relname,
17761777
pg_stat_get_numscans(c.oid) AS seq_scan,
1778+
pg_stat_get_lastscan(c.oid) AS last_seq_scan,
17771779
pg_stat_get_tuples_returned(c.oid) AS seq_tup_read,
17781780
(sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan,
1781+
max(pg_stat_get_lastscan(i.indexrelid)) AS last_idx_scan,
17791782
((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch,
17801783
pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
17811784
pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
@@ -2107,6 +2110,7 @@ pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid,
21072110
pg_stat_all_indexes.relname,
21082111
pg_stat_all_indexes.indexrelname,
21092112
pg_stat_all_indexes.idx_scan,
2113+
pg_stat_all_indexes.last_idx_scan,
21102114
pg_stat_all_indexes.idx_tup_read,
21112115
pg_stat_all_indexes.idx_tup_fetch
21122116
FROM pg_stat_all_indexes
@@ -2115,8 +2119,10 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid,
21152119
pg_stat_all_tables.schemaname,
21162120
pg_stat_all_tables.relname,
21172121
pg_stat_all_tables.seq_scan,
2122+
pg_stat_all_tables.last_seq_scan,
21182123
pg_stat_all_tables.seq_tup_read,
21192124
pg_stat_all_tables.idx_scan,
2125+
pg_stat_all_tables.last_idx_scan,
21202126
pg_stat_all_tables.idx_tup_fetch,
21212127
pg_stat_all_tables.n_tup_ins,
21222128
pg_stat_all_tables.n_tup_upd,
@@ -2151,6 +2157,7 @@ pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid,
21512157
pg_stat_all_indexes.relname,
21522158
pg_stat_all_indexes.indexrelname,
21532159
pg_stat_all_indexes.idx_scan,
2160+
pg_stat_all_indexes.last_idx_scan,
21542161
pg_stat_all_indexes.idx_tup_read,
21552162
pg_stat_all_indexes.idx_tup_fetch
21562163
FROM pg_stat_all_indexes
@@ -2159,8 +2166,10 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
21592166
pg_stat_all_tables.schemaname,
21602167
pg_stat_all_tables.relname,
21612168
pg_stat_all_tables.seq_scan,
2169+
pg_stat_all_tables.last_seq_scan,
21622170
pg_stat_all_tables.seq_tup_read,
21632171
pg_stat_all_tables.idx_scan,
2172+
pg_stat_all_tables.last_idx_scan,
21642173
pg_stat_all_tables.idx_tup_fetch,
21652174
pg_stat_all_tables.n_tup_ins,
21662175
pg_stat_all_tables.n_tup_upd,

0 commit comments

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