Geek Answers Handbook

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

I use Heroku Postgres and want to see what the effect of adding additional indexes to my database is.

I am querying pg_stat_user_tables to find out what percentage of queries are using indexes, but I think I have been using statistics since I created the database.

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

 ERROR: must be superuser to reset statistics counters

Is there any other way to achieve this using the Heroku Postgres production database?

+7
source share
2 answers

How easy is it for you to reproduce the scripts or tests that will execute these queries? Could you do the following?

  • Create a new test application and test database on heroku
  • Deploy the old version of your application with the old indexes (and a representative subset of the data if copying your entire database is not possible)
  • Run your queries or tests or run your application.
  • Pay attention to db statistics
  • Drop the test database and create a new one.
  • Fill a new test database with your data and add new indexes
  • Specify the test application in the new database
  • Run your queries or tests again
  • Check out the new db stats

We hope you can easily create a representative dataset that will allow you to easily test your queries.

+2
source

Today I found out through a support request that this has now been added:

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

heroku pg:stats_reset

+1
source

More articles:


All Articles