Skip to content

Commit c037471

Browse files
anarazelanarazel
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., when evaluating whether an index is still required. It was already possible to infer 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, track that time in each relation's pgstat entry. To minimize overhead a) the timestamp is updated only when the backend pending stats entry is flushed to shared stats b) the last transaction's stop timestamp is used as the timestamp. 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 parent 309b2cf commit c037471

File tree

10 files changed

+356
-2
lines changed

10 files changed

+356
-2
lines changed

‎doc/src/sgml/monitoring.sgml

+30
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

+3
Original file line numberDiff line numberDiff line change
@@ -656,8 +656,10 @@ CREATE VIEW pg_stat_all_tables AS
656656
N.nspname AS schemaname,
657657
C.relname AS 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))::bigint AS 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.relname AS relname,
775777
I.relname AS 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 C JOIN

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

+6
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+
TimestampTz t = 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

+13
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+
Oid relid = 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

+1-1
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202209291
60+
#define CATALOG_VERSION_NO 202210141
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

+4
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

+2-1
Original file line numberDiff line numberDiff line change
@@ -242,7 +242,7 @@ typedef struct PgStat_TableXactStatus
242242
* ------------------------------------------------------------
243243
*/
244244

245-
#define PGSTAT_FILE_FORMAT_ID 0x01A5BCA8
245+
#define PGSTAT_FILE_FORMAT_ID 0x01A5BCA9
246246

247247
typedef struct PgStat_ArchiverStats
248248
{
@@ -354,6 +354,7 @@ typedef struct PgStat_StatSubEntry
354354
typedef struct PgStat_StatTabEntry
355355
{
356356
PgStat_Counter numscans;
357+
TimestampTz lastscan;
357358

358359
PgStat_Counter tuples_returned;
359360
PgStat_Counter tuples_fetched;

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

+9
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)