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.

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.