Agent skill · data analytics · jeremylongshore
analyzing-database-indexes
Process use when you need to work with database indexing.
Why this skill is useful
Adds executable scripts for index analysis and optimization that aren't in public documentation.
What it needs
Requires mysql, psql installed locally. About 4k tokens when loaded. Last updated 2026-08-07. 2,604 stars on the source repository.
What this skill does
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 pgstatuserindexes, pgstatusertables, and pgstatstatements (PostgreSQL) or performanceschema and sys schema (MySQL) pgstatstatements extension enabled for PostgreSQL query statistics psql 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 pgstatreset() Instructions 1. Identify tables with high sequential scan activity (candidates for missing indexes): PostgreSQL: SELECT relname, seqscan, seqtupread, idxscan, nlivetup FROM pgstatusertables WHERE seqscan > 100 AND nlivetup > 10000 ORDER BY seqtupread DESC LIMIT 20 A table with high seqscan count and high seqtupread relative to nlivetup is scanning most of the table repeatedly 2. Find the queries causing sequential scans by correlating with pgstatstatements: SELECT query, calls, meanexectime, rows FROM pgstatstatements WHERE query ILIKE '%tablename%' ORDER BY meanexectime DESC LIMIT 10 Run EXPLAIN (ANALYZE, BUFFERS) on the top queries to confirm sequential scan usage 3. Analyze query WHERE clauses and JOIN conditions to determine which columns need indexes. Extract the filtering columns and their selectivity: SELECT columnname, ndistinct, correlation FROM pgstats WHERE tablename = 'targettable' High ndistinct (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 4. Recommend composite indexes for multi-column queries. …
How to use it
Reference it in AdaL, Claude Code, Cursor or any coding agent — nothing to install:
@skills jeremylongshore/analyzing-database-indexes--279304