| name | analyzing-database-indexes |
| description | Process use when you need to work with database indexing.
This skill provides index design and optimization with comprehensive guidance and automation.
Trigger with phrases like "create indexes", "optimize indexes",
or "improve query performance".
|
| allowed-tools | Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*) |
| version | 1.27.0 |
| author | Jeremy Longshore <jeremy@intentsolutions.io> |
| license | MIT |
| tags | ["database","performance","analyzing-database"] |
| compatibility | Designed for Claude Code, also compatible with Codex and OpenClaw |
Database Index Advisor
Overview
Analyze database index usage, identify missing indexes causing sequential scans, detect redundant or unused indexes wasting write performance, and recommend optimal index configurations for PostgreSQL and MySQL.
Prerequisites
- Database credentials with access to
pg_stat_user_indexes, pg_stat_user_tables, and pg_stat_statements (PostgreSQL) or performance_schema and sys schema (MySQL)
pg_stat_statements extension enabled for PostgreSQL query statisticspsql or mysql CLI for executing analysis queries- Representative workload running (analysis during off-peak hours may miss important query patterns)
- At least 24 hours of statistics accumulation since the last
pg_stat_reset()
Instructions
-
Identify tables with high sequential scan activity (candidates for missing indexes):
- PostgreSQL:
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup FROM pg_stat_user_tables WHERE seq_scan > 100 AND n_live_tup > 10000 ORDER BY seq_tup_read DESC LIMIT 20
- A table with high
seq_scan count and high seq_tup_read relative to n_live_tup is scanning most of the table repeatedly
-
Find the queries causing sequential scans by correlating with pg_stat_statements:
SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements WHERE query ILIKE '%table_name%' ORDER BY mean_exec_time DESC LIMIT 10- Run
EXPLAIN (ANALYZE, BUFFERS) on the top queries to confirm sequential scan usage
-
Analyze query WHERE clauses and JOIN conditions to determine which columns need indexes. Extract the filtering columns and their selectivity:
SELECT column_name, n_distinct, correlation FROM pg_stats WHERE tablename = 'target_table'- High
n_distinct (close to row count) indicates good index selectivity
correlation close to 1.0 or -1.0 suggests the column benefits from a B-tree index
-
Recommend composite indexes for multi-column queries. Follow the equality-first, range-second ordering:
- Place columns used with operators first in the index
