pg_stat_reset_single_table_counters()

A function for resettting the statistics of a table or index

pg_stat_reset_single_table_counters() is a system function for resetting the statistics for a single table or index to zero.

pg_stat_reset_single_table_counters() was added in PostgreSQL 9.0.

Usage

pg_stat_reset_single_table_counters ( oid ) → void

No error is raised if an invalid OID is provided.

By default pg_stat_reset_single_function_counters() can only be executed by a superuser. Other users can be granted permission via the EXECUTE privilege.

Change history

Examples

Basic usage example for pg_stat_reset_single_table_counters():

proddb=# SELECT * FROM pg_stat_user_tables WHERE relname = 'object';
-[ RECORD 1 ]-------+------------------------------
relid               | 16956
schemaname          | app
relname             | object
seq_scan            | 905147
seq_tup_read        | 4148119915
idx_scan            | 1532505226
idx_tup_fetch       | 1953595589
n_tup_ins           | 4922
n_tup_upd           | 10738
n_tup_del           | 11
n_tup_hot_upd       | 10689
n_live_tup          | 4906
n_dead_tup          | 158
(...)

proddb=# SELECT pg_stat_reset_single_table_counters('app.object'::regclass);
 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

proddb=# SELECT * FROM pg_stat_user_tables WHERE relname = 'object';
-[ RECORD 1 ]-------+-------
relid               | 16956
schemaname          | app
relname             | object
seq_scan            | 0
seq_tup_read        | 0
idx_scan            | 1532505226
idx_tup_fetch       | 1953595589
n_tup_ins           | 0
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
(...)

Note that the index counters are only reset by calls to pg_stat_reset_single_table_counters() specifiying each index on the base relation.

Providing an invalid OID does not result in an error being raised or any other kind of warning:

postgres=# SELECT pg_stat_reset_single_table_counters(-1);
 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

Categories

Server statistics, System function

See also

pg_stat_reset(), pg_stat_reset_shared(), pg_stat_reset_single_function_counters(), pg_stat_reset_slru(), pg_stat_reset_replication_slot()