PostgreSQL Performance – Impact of Running pg_stat_reset

postgresql

I'm doing some index analysis on our database to see if there are indexes we can remove. To do this I want to run select pg_stat_reset(); to reset all stat counters back to zero, then just wait a week and see which indexes haven't been used.

Does running pg_stat_reset() also reset row count statistics? Will this affect the query planner?

Is it recommended to rerun Analyze public.#table# on all tables after you reset stats?

Thanks!

Best Answer

pg_stat_reset will not affect query plans (it resets monitoring, not data-distribution, statistics), but it will cause autovacuum to think that no tables have recently been vacuumed, and so autovacuum activity will be elevated for a while. Whether this is a problem depends on the specifics of your data and how it's used.

This is a good article explaining the difference between the statistics used by the planner vs those that pg_stat_reset() affects, as well as how pg_stat_reset interacts with autovacuum:

https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql

I'd like to suggest an alternative, however: Just run CREATE TABLE statistics_snapshot_TODAYS_YYYYMMDD AS SELECT * FROM pg_stat_user_indexes (replace pg_stat_user_indexes with whatever you're using if you are using a different pg_stat_* view), and then, in a week, do join it with the view and do a subtraction. This will give you week-over-week deltas, without having to reset anything.

Sketch of the reporting query (not tested, but hopefully you get the idea):

SELECT
    stats_now.schemaname, stats_now.indexrelname,
    stats_now.idx_scan - stats_weekago.idx_scan idx_scan_delta,
    stats_now.idx_tup_read - stats_weekago.idx_tup_read idx_tup_read_delta,
    stats_now.idx_tup_fetch - stats_weekago.idx_tup_fetch idx_tup_fetch_delta
FROM pg_stat_user_indexes stats_now
JOIN statistics_snapshot_20201006 stats_weekago ON
    stats_now.indexrelid=stats_weekago.indexrelid;
Related Question