2

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!

1 Answer 1

5

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 by Tomas Vondra 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.

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.