Optimize Database Indexes for Peak Performance

This skill provides expertise in database index optimization, with deep knowledge of query performance tuning, index design patterns, and internals across PostgreSQL, MySQL, SQL Server, and Oracle, understanding B-tree structures, query execution plans, and statistical analysis to build optimal indexing strategies. Index analysis and strategy starts every optimization with studying query patterns and frequency from slow-query logs, table sizes/growth/data distribution, existing index redundancy and effectiveness, read/write ratios per table, and column cardinality/selectivity - using database-specific tools like EXPLAIN (ANALYZE, BUFFERS) and index usage statistics views (e.g. pg_stat_user_indexes) to ground decisions in real data.

Composite index design follows the equality-range-sort rule: equality conditions first, then range conditions, then sort columns last, matching the actual WHERE/ORDER BY pattern of the target query - plus dedicated indexes supporting JOIN foreign-key conditions. Partial and filtered indexes handle uneven data distribution, indexing only active records or a recent date range rather than the full table to keep the index small and fast. Covering indexes with INCLUDE columns minimize table lookups by embedding frequently selected non-key columns directly in the index.

Index maintenance and monitoring covers regular health checks (index size, bloat, insert/update counts) and identifying unused indexes (zero scans, zero tuple reads) that should be dropped, plus scheduled maintenance (concurrent reindexing for heavily updated tables, index reorganization for fragmentation). Advanced optimization techniques cover expression indexes on computed values (e.g. tax-inclusive totals) and case-insensitive text search indexes (indexing LOWER(email)), plus intelligent index-type selection - hash indexes for large-table equality lookups, GIN/GiST for full-text search and arrays, partitioned indexes for time-series data, and functional GIN indexes for JSON attribute queries. Performance validation compares execution plans before/after index creation, tracks query time improvements, monitors index-scan-to-sequential-scan ratio, and measures write-path impact - always tested in staging and deployed during low-traffic windows.

Use this skill when optimizing database query performance through indexing - designing composite/partial/covering indexes, cleaning up unused indexes, or choosing the right index type (GIN, hash, expression) for a specific access pattern. It is well suited to production databases with real slow-query logs and usage statistics to analyze. It is not meant for a brand-new database with no query patterns yet to analyze, or for trivial tables too small for indexing to matter.

Input: slow-query logs, existing index usage statistics, and the target query patterns.

Output: composite/partial/covering index definitions, index maintenance recommendations, and before/after performance validation. Example composite index following equality-range-sort:

-- Query pattern: WHERE status = 'pending' AND created_date >= '2024-01-01' ORDER BY priority DESC, created_date
CREATE INDEX idx_orders_status_date_priority ON orders
(status, created_date, priority DESC);

Works with PostgreSQL, MySQL, SQL Server, and Oracle index DDL and their respective query-plan/statistics tooling (EXPLAIN ANALYZE, pg_stat_user_indexes).

Database engineers and DBAs optimizing query performance through indexing, and teams that need composite/partial/covering index strategies grounded in real usage statistics rather than guesswork.