PostgreSQL 11.1中pg_stat_all_indexes表统计数据存储时长咨询

Great question—this is a common pain point when relying on index usage stats to decide whether to drop an index. Let’s break down how to check the stats retention window, why you’re seeing those wild fluctuations, and how to make more informed decisions.

How to Check the Statistics Collection Start Time #

PostgreSQL tracks when its statistics were last reset in the pg_stat_database view. The stats_reset column gives you the exact timestamp when the current set of stats started being accumulated. Run this query to get that critical timestamp:

SELECT stats_reset FROM pg_stat_database LIMIT 1;

The time between this stats_reset value and the current time is how long your current pg_stat_all_indexes data has been stored. All index usage counts you see are only from this point forward.

Why Your Stats Are Fluctuating (From Thousands to 0) #

That sudden drop to zero almost always means the statistics were reset. Here are the most common triggers:

• Database restart: PostgreSQL automatically resets all statistics when the server boots up.
• Manual reset: A superuser ran the pg_stat_reset() function, which clears all accumulated stats in one go.
• Automated scripts: Some monitoring or maintenance tools might be configured to run pg_stat_reset() on a schedule—check your cron jobs or database automation workflows to confirm.

Tips for Safer Index Removal Decisions #

Relying on a single, potentially short stats window can lead to costly mistakes. Here’s how to build more reliable data for your decisions:

• Track historical stats: Export key columns from pg_stat_all_indexes (like idx_scan, idx_tup_read, idx_tup_fetch) to a separate table on a regular basis (daily or weekly). This lets you spot usage trends over weeks or months, not just the current reset window.
• Avoid unnecessary resets: If you or your team are manually running pg_stat_reset(), stop unless you have a specific reason. If an automated tool is doing it, adjust its schedule to align with your analysis timeline.
• Combine with other metrics: Don’t just look at scan counts. Check the index’s size with pg_indexes_size('your_index_name') to see if it’s wasting space, and use EXPLAIN ANALYZE on critical queries to confirm if the index is actually being leveraged.
• Test before dropping: Instead of deleting an index immediately, mark it as unusable first:

  ALTER INDEX your_index_name SET UNUSABLE;
  

  Monitor performance for a week or two—if there’s no slowdown, you can safely drop it. If queries start lagging, just rebuild the index with ALTER INDEX your_index_name REBUILD;.

内容的提问来源于stack exchange，提问作者bronek