Horton

all_shared_tables.sql

application/octet-stream

Download
Filename: all_shared_tables.sql
Type: application/octet-stream
Part: 0
Message: Re: Support reset of Shared objects statistics in "pg_stat_reset" function 
-- see all the stats before reset
SELECT * FROM pg_statio_all_tables  where relid = 'pg_database'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_tablespace'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_authid'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_auth_members'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdescription'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdepend'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shseclabel'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_db_role_setting'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_replication_origin'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_subscription'::regclass::oid;

-- now resets stats for all the shared tables
select pg_stat_reset();

-- now again check stats for all the shared tables
SELECT * FROM pg_statio_all_tables  where relid = 'pg_database'::regclass::oid; 
SELECT * FROM pg_statio_all_tables  where relid = 'pg_tablespace'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_authid'::regclass::oid;   
SELECT * FROM pg_statio_all_tables  where relid = 'pg_auth_members'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdescription'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdepend'::regclass::oid; 
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shseclabel'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_db_role_setting'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_replication_origin'::regclass::oid;
SELECT * FROM pg_statio_all_tables  where relid = 'pg_subscription'::regclass::oid;

--now reset stats one by one for all tables
SELECT * FROM pg_statio_all_tables  where relid = 'pg_database'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_database'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_database'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_tablespace'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_tablespcae'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_tablespace'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_authid'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_authid'::regclass::oid);
 SELECT * FROM pg_statio_all_tables  where relid = 'pg_authid'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_auth_members'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_auth_members'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_auth_members'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdescription'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_shdescription'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdescription'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdepend'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_shdepend'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_shdepend'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_shseclabel'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_shseclabel'::regclass::oid);
 SELECT * FROM pg_statio_all_tables  where relid = 'pg_shseclabel'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_db_role_setting'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_db_role_setting'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_db_role_setting'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_replication_origin'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_replication_origin'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_replication_origin'::regclass::oid;

SELECT * FROM pg_statio_all_tables  where relid = 'pg_subscription'::regclass::oid;
select pg_stat_reset_single_table_counters('pg_subscription'::regclass::oid);
SELECT * FROM pg_statio_all_tables  where relid = 'pg_subscription'::regclass::oid;