pg_stat_statements_reset() permission denied on Google Cloud SQL PostrgreSQL

Question

Main Issue

I'm unable to execute the function SELECT pg_stat_statements_reset(); in order to profile changes in query optimization due to insufficient permissions. The error message reads: permission denied for function pg_stat_statements_reset

I would like to know if there are any other ways to reset the pg_stats on Cloud SQL PostgreSQL?

Environment

• PG version: PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
• Platform: Google Cloud SQL PostgreSQL
• User: default postgres user with cloudsqlsuperuser role

Attempted Steps

I have found a previous answer on this topic which suggested that pg_stat_statements_reset() should work from the default postgres user created through the cloud console. However, the listed solution does not work, it returns the same permission denied error

Related Question

Pallaton

Answer 1

Google Cloud SQL supports several modules for PostgreSQL to extend its functionality.

One of the extensions (modules) is: pg_stat_statements. It allows tracking execution statistics of SQL statements executed by a server. To reset statistics function pg_stat_statements_reset() is used.

Before using extension (module), it has to be installed:

1. Connect to the PostgreSQL instance from Cloud Shell using default user: postgres

gcloud sql connect [INSTANCE_ID] --user=postgres

2. When connected to the database, create extension pg_stat_statements

CREATE EXTENSION pg_stat_statements

3. Execute the function to reset statistics. By default, It can only be executed by superusers:

SELECT pg_stat_statements_reset()

4. (optional) Grant privilege for stats resetting to other users:

GRANT EXECUTE ON FUNCTION pg_stat_statements_reset TO xuser;

Gobelin

I ran pg_stat_statements_reset but the stats on my index usage didn't reset to zero as expected – Glyph

You can reset index using pg_stat_reset() that is not supported on Cloud SQL. Autovaccum, needs pg_stat_user_indexes to work. Maybe this article will help you understand better usage of the indexes. – Gobelin

Right, so if I want stats on indexes I guess I need to snapshot them from time to time and look at the differences. – Glyph

I had to remove the parentheses in the GRANT statement in order for it to execute properly – Decanter

Answer 2

With snapshots:

DROP TABLE IF EXISTS stat_snap_1;
DROP TABLE IF EXISTS stat_snap_2;

-- first time
CREATE TABLE stat_snap_1 AS SELECT * FROM pg_stat_statements WHERE queryid IS NOT NULL;
-- second time
CREATE TABLE stat_snap_2 AS SELECT * FROM pg_stat_statements WHERE queryid IS NOT NULL;

SELECT s2.calls - s1.calls, s2.total_time - s1.total_time, s2.*, s1.*
FROM stat_snap_2 s2
FULL OUTER JOIN stat_snap_1 s1 ON s1.queryid = s2.queryid
ORDER BY s2.total_time - s1.total_time DESC NULLS LAST;

Isidora