Postgres Tells You Your Query Was Slow. Not Which Index Was Wasted.

Short summary

PostgreSQL's pg_stat_user_indexes shows which indexes are scanned but not whether specific queries use them or waste disk resources. The author built a 3-file query logger capturing EXPLAIN plans alongside execution metrics and found 78% of their production database's index disk maintaining unused indexes. Key principle: fire-and-forget logging with sampling prevents monitoring from slowing down production.

• •pg_stat_user_indexes is blind to actual query-index relationships and wasted indexes
• •Custom query wrapper logs execution plans and metrics in one queryable table
• •First run discovered 20 of 51 indexes (78% of disk space) had zero scans

Generated with AI, which can make mistakes.