How to I reset the statistics for a Heroku Postgres database?

Issue

I'd like to reset the query planning and execution statistics to measure query performance after making database changes.

Resolution

Heroku Postgres installs the pg_stat_statements extension by default. This extension provides the pg_stat_statements view, where Postgres records different statistics about query execution and planning. Different Heroku Postgres features such as Expensive queries, pg:diagnose, pg:outliers or pg:long-running-queries are based on these statistics.

To reset the pg_stat_statements statistics, run the following query through a psql session, depending on your database plan:

• Essential and Advanced plans:
  SELECT _heroku.pg_stat_statements_reset();
• Standard-tier and higher plans (Classic plans):
  SELECT pg_stat_statements_reset();

Alternatively, you can also reset query statistics via the CLI in two different ways:

1. Using the heroku pg:outliers command:

   heroku pg:outliers ADDON_NAME --reset -a example-app
   

2. Using the heroku pg:stats-reset command from the Heroku pg-extras CLI plugin:

   heroku plugins:install heroku-pg-extras
   heroku pg:stats-reset ADDON_NAME -a example-app