How can I reset pg_stat_user_tables on Heroku? (pg_stat_reset() requires superuser)

Question

I am using Heroku Postgres and want to see what the effect has been of adding some additional indexes to my database.

I am querying pg_stat_user_tables to see what proportion of queries are using indexes, but I believe that is using statistics from ever since I created the database.

I understand that running SELECT pg_stat_reset(); would reset these statistics, but when I try to run that in the pg:psql console I get the error:

ERROR:  must be superuser to reset statistics counters

Is there another way to achieve this with a Heroku Postgres production database?

Answer 1

I found out via a support request today that this has now been added:

heroku plugins:install git://github.com/heroku/heroku-pg-extras.git

heroku pg:stats_reset

Answer 2

How easy is it for you to reproduce the scenarios or tests that will exercise those queries? Could you do the following?

1. Create a new test app and test database on heroku
2. Deploy the old version of your app, with the old indexes (and a representative subset of data if copying your entire database is not feasible)
3. Run your queries or tests, or exercise your app
4. Note the db statistics
5. Drop the test database and create a new one
6. Fill the new test database with your data, and add the new indexes
7. Point the test app at the new database
8. Run your queries or tests again
9. Note the new db statics

Hopefully you are able to easily create a representative set of data that will let you test your queries more easily.