Reset pg_stat_statements with Google Cloud SQL

Question

I am using a PostgreSQL instance running in Google Cloud SQL.

I am trying to reset the pg_stat_statements because we made improvements to our tables and added some index. Unfortunately, when I try to run the following command using the main postgres user:

select pg_stat_reset();

I get the following error message:

ERROR:  permission denied for function pg_stat_reset

I understand that it's a managed service and some things need to be walled off, but it's important to be able to reset pg_stat so you can tune the database. Basically if you make a change, you need to be able to reset pg_stat so that its output isn't polluted from data prior to the change. Even in a managed service, this kind of tuning is critical to be able to do.

Anyone know how I can get this to work with Google Cloud SQL?

Thanks.

Wichita

Answer 1

I was able to ask GoogleCloud Support about this and here's what they got back with:

pg_stat_reset() needs superuser privileges. But it is not supported in CloudSQL. We recommand that you use pg_stat_statements_reset() function instead to reset the statistics. To do this, either user has to be created from Cloud Console (i.e. Default PostgreSQL user), or EXECUTE permission on this function should be granted by a default PostgreSQL user by executing the following command: grant execute on function pg_stat_statements_reset() to ;

I was able to use this command successfully using the postgres user. Note that this function must be run on the database where pg_stat_statement was enabled.

Wichita

After running pg_stat_statements_reset() as the default postgres user my index usage statistics didn't get reset to zero. Is there a way to achieve this? – Tomi

Answer 2

Altough you can't run the function you want in CloudSQL, but you can achieve the same effect by resetting individual index stats by using pg_stat_reset_single_table_counters function. You need to be logged in as user with cloudsqlsuperuser role and you can't grant EXECUTE on this function to another user. To reset all indexes for non-postgres tables run the below query:

select relname, pg_stat_reset_single_table_counters(oid)
from pg_class 
where reltype=0 
and relname not like 'pg_%';

Flowing