Index Health and Usage Analysis

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you audit your PostgreSQL indexes. Let me start by exploring your directory to understand your setup. I don't see a PostgreSQL database connection in your current directory. Let me create a comprehensive SQL script you can run to audit your PostgreSQL indexes for both unused indexes and missing index opportunities. I've created a comprehensive PostgreSQL index audit script. Here's how to audit your indexes: ## **PART 1: Finding Unused/Rarely Used Indexes (Safe to Drop)** ### 1. Indexes Never Used ```sql SELECT schemaname, tablename, indexname, idx_scan as index_scans, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey' ORDER BY pg_relation_size(indexrelid) DESC; ``` ### 2. Rarely Used Indexes (< 50 scans) ```sql SELECT schemaname, tablename, indexname, idx_scan as index_scans, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes WHERE idx_scan < 50 AND idx_scan > 0 AND indexrelname NOT LIKE '%_pkey' ORDER BY idx_scan ASC; ``` ### 3. Total Size of Unused Indexes ```sql SELECT pg_size_pretty(SUM(pg_relation_size(indexrelid))) as total_waste, COUNT(*) as unused_count FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'; ``` ## **PART 2: Finding Missing Index Opportunities** ### 4. Tables with High Sequential Scans ```sql SELECT schemaname, tablename, seq_scan, idx_scan, ROUND(100.0 * idx_scan / NULLIF(seq_scan + idx_scan, 0), 2) as index_usage_pct, n_live_tup as rows, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as size FROM pg_stat_user_tables WHERE seq_scan > 0 AND n_live_tup > 10000 ORDER BY seq_scan DESC LIMIT 25; ``` ### 5. Foreign Keys Without Indexes ```sql SELECT c.conrelid::regclass as table_name, STRING_AGG(a.attname, ', ') as fk_columns FROM pg_constraint c CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS x(attnum, n) JOIN pg_attribute a ON a.attnum = x.attnum AND a.attrelid = c.conrelid WHERE c.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND c.conkey[1] = i.indkey[0] ) GROUP BY c.conrelid, c.conname; ``` ### 6. Most Expensive Queries (requires pg_stat_statements) ```sql -- Enable first: CREATE EXTENSION IF NOT EXISTS pg_stat_statements; SELECT ROUND(total_exec_time::numeric, 2) as total_ms, calls, ROUND(mean_exec_time::numeric, 2) as avg_ms, LEFT(query, 100) as query_preview FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; ``` ## **Key Recommendations:** **Safe to drop if:** - idx_scan = 0 and the index has existed for weeks/months - It's not a primary key or unique constraint - It's not supporting a foreign key relationship - You've verified it's not used in cron jobs or batch processes **Consider adding indexes for:** - Foreign key columns (performance boost for JOINs and DELETE CASCADE) - Columns in WHERE clauses with high seq_scan counts - Columns used in ORDER BY on large tables - Composite indexes for queries filtering on multiple columns **Before dropping, test:** ```sql -- Check when stats were last reset SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); ``` Would you like me to help you connect to your database and run these queries?

I'll help you audit your PostgreSQL indexes. Let me first check what's in your current directory to understand your database setup. I see you have an MCP server configured. Let me check what database schema tools are available to help with the PostgreSQL index audit. Let me search for PostgreSQL index audit insights and best practices. I'll create a comprehensive PostgreSQL index audit script for you. This will help you identify unused indexes and potential performance improvements. I'll provide you with a comprehensive PostgreSQL index audit guide. Here are the key queries and strategies: ## **1. Find Unused Indexes (Safe to Drop)** ```sql -- Indexes with zero or very low usage SELECT schemaname || '.' || tablename AS table, indexrelname AS index, idx_scan AS index_scans, pg_size_pretty(pg_relation_size(indexrelid)) AS size, pg_get_indexdef(indexrelid) AS definition FROM pg_stat_user_indexes WHERE idx_scan < 50 -- Adjust threshold AND indexrelname NOT LIKE '%_pkey' -- Keep primary keys ORDER BY pg_relation_size(indexrelid) DESC; ``` **Safe to drop if:** 0 scans, not a primary key/unique constraint, and been running for weeks/months. ## **2. Find Duplicate/Redundant Indexes** ```sql -- Indexes on the same columns SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS wasted_size, array_agg(indexrelname) AS duplicate_indexes, tablename FROM ( SELECT indexrelid::regclass AS idx, tablename, indexrelname, regexp_replace(pg_get_indexdef(indexrelid), '^[^\(]+\((.*)\)$', '\1') AS columns FROM pg_stat_user_indexes ) sub GROUP BY tablename, columns HAVING COUNT(*) > 1; ``` ## **3. Tables Missing Indexes (High Sequential Scans)** ```sql -- Tables frequently scanned sequentially SELECT schemaname || '.' || tablename AS table, seq_scan AS sequential_scans, n_live_tup AS estimated_rows, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS size, ROUND(100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS pct_sequential FROM pg_stat_user_tables WHERE seq_scan > 100 AND n_live_tup > 10000 ORDER BY seq_scan * n_live_tup DESC LIMIT 20; ``` **Action:** Run `EXPLAIN ANALYZE` on queries hitting these tables to identify missing indexes on WHERE/JOIN columns. ## **4. Find Slow Queries Needing Indexes** ```sql -- Requires pg_stat_statements extension -- CREATE EXTENSION IF NOT EXISTS pg_stat_statements; SELECT ROUND(mean_exec_time::numeric, 2) AS avg_ms, calls, ROUND(total_exec_time::numeric, 2) AS total_ms, LEFT(query, 100) AS query_preview FROM pg_stat_statements WHERE mean_exec_time > 100 -- Queries slower than 100ms ORDER BY mean_exec_time DESC LIMIT 20; ``` Then use `EXPLAIN ANALYZE` on these queries to see if sequential scans could be replaced with index scans. ## **5. Index Usage Summary** ```sql -- Overall health check SELECT COUNT(*) AS total_indexes, COUNT(*) FILTER (WHERE idx_scan = 0) AS unused, pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_size, pg_size_pretty(SUM(pg_relation_size(indexrelid)) FILTER (WHERE idx_scan = 0)) AS unused_size FROM pg_stat_user_indexes WHERE indexrelname NOT LIKE '%_pkey'; ``` ## **Key Actions:** 1. **Drop unused indexes safely:** ```sql DROP INDEX CONCURRENTLY index_name; -- Won't block queries ``` 2. **Before dropping, verify it's not critical:** - Check if it enforces UNIQUE constraints - Test in staging first - Save the definition: `SELECT pg_get_indexdef('index_name'::regclass);` 3. **Create missing indexes:** ```sql CREATE INDEX CONCURRENTLY idx_name ON table(column); ``` 4. **Reset stats for fresh data:** ```sql SELECT pg_stat_reset(); -- Then wait for typical workload ``` Would you like me to create a full audit script file, or do you have specific tables/schemas you'd like to focus on?

Start by checking `postgresql-index-rel-scans` to find indexes with zero scans—these have never been used and are pure overhead on every write. The `unused-indexes-waste-performance` insight explains that each unused index still imposes write costs on INSERT, UPDATE, and DELETE operations while consuming storage. Focus first on large unused indexes (>100MB) as they have the most impact. Before dropping, verify the index isn't relied upon for constraint enforcement (unique/primary key indexes will show up here too).

Compare `postgresql-sequential-scans` to `postgresql-index-rel-scans` on each table—if you're seeing high sequential scan counts on tables with >100K rows, you likely have missing indexes. Also check `sequential-tuple-read` volume: scanning millions of rows repeatedly is a red flag. The `missing-indexes-causing-sequential-scans` insight indicates these patterns cause query slowdowns. Cross-reference with `postgresql-queries-duration-max` to identify which slow queries are driving the seq scans, then add indexes on their WHERE/JOIN predicates.

Examine your index definitions to find redundant coverage—for example, an index on (col_a, col_b) makes a standalone index on (col_a) redundant in most cases. The `duplicate-or-redundant-indexes` insight highlights that these waste the same write overhead as unused indexes. Query pg_indexes to list all indexes per table, then look for overlapping column sets. This is especially common after migrations or when multiple developers add indexes independently over time.

Review `postgresql-index-bloat` to find indexes with >30% bloat—these consume excessive disk space and slow down scans. The `index-bloat-requiring-reindex` insight indicates this typically happens after heavy UPDATE/DELETE activity. Bloated indexes can be larger than the table itself and still perform poorly. Prioritize REINDEX on bloated indexes that show high `postgresql-index-rel-scans`, as these are actively used and benefiting from optimization will have immediate impact.

Compare `index-tuple-read` to `index-tuple-fetch` for each index—a large ratio (e.g., >10:1) means the index is reading many entries but fetching few rows, indicating poor selectivity. Low-selectivity indexes on columns with few distinct values (like boolean flags or status enums) often hurt more than they help. The `opportunities-for-index-optimization` insight suggests that these indexes may need composite columns added or may be candidates for removal in favor of partial indexes on the most common predicates.

If you're experiencing slow INSERT/UPDATE/DELETE operations, count your total indexes per table and consider the cumulative write cost. The `high-index-write-overhead` insight explains that each index must be maintained on every write operation. Tables with >10 indexes, especially those receiving heavy write traffic, should be audited carefully. Use the findings from previous steps to prune unused, redundant, and low-value indexes to reduce this overhead. The `need-for-index-creation` insight reminds us that balance matters—don't over-index write-heavy tables.