Index Advisor

Analyze query workloads, identify missing indexes, detect unused indexes, and recommend optimal indexing strategies with automated index impact analysis and maintenance scheduling for production databases.

When to Use This Command

Use /index-advisor when you need to:

• Optimize slow queries with proper indexing strategies
• Analyze database workload for missing index opportunities
• Identify and remove unused indexes consuming storage and write performance
• Design composite indexes for multi-column query patterns
• Implement covering indexes to eliminate table lookups
• Monitor index bloat and schedule maintenance (REINDEX, VACUUM)

DON'T use this when:

• Database is small (<1GB) with minimal query load
• All queries are simple primary key lookups
• You're looking for application-level query issues (use query optimizer instead)
• Database doesn't support custom indexes (some managed databases)

Design Decisions

This command implements workload-based index analysis because:

• Real query patterns reveal actual index opportunities
• EXPLAIN ANALYZE provides accurate index impact estimates
• Unused index detection prevents unnecessary write overhead
• Composite index recommendations reduce total index count
• Covering indexes eliminate expensive table lookups (3-10x speedup)

Alternative considered: Static schema analysis

• Only analyzes table structure, not query patterns
• Can't estimate real-world performance impact
• May recommend indexes that won't be used
• Recommended only for initial schema design

Alternative considered: Manual EXPLAIN analysis

• Requires deep SQL expertise for every query
• Time-consuming and error-prone
• No systematic unused index detection
• Recommended only for ad-hoc optimization

Prerequisites

Before running this command:

1. Access to database query logs or slow query log
2. Permission to run EXPLAIN ANALYZE on queries
3. Monitoring of database storage and I/O metrics
4. Understanding of application query patterns
5. Maintenance window for index creation (for large tables)

Implementation Process

Step 1: Collect Query Workload Data

Capture real production queries from logs or pg_stat_statements.

Step 2: Analyze Query Execution Plans

Run EXPLAIN ANALYZE to identify sequential scans and suboptimal query plans.

Step 3: Generate Index Recommendations

Identify missing indexes, composite index opportunities, and covering indexes.

Step 4: Simulate Index Impact

Estimate query performance improvements with hypothetical indexes.

Step 5: Implement and Monitor Indexes

Create recommended indexes and track query performance improvements.

Output Format

The command generates:

• analysis/missing_indexes.sql - CREATE INDEX statements for missing indexes
• analysis/unused_indexes.sql - DROP INDEX statements for unused indexes
• reports/index_impact_report.html - Visual impact analysis with before/after metrics
• monitoring/index_health.sql - Queries to monitor index bloat and usage
• maintenance/reindex_schedule.sh - Automated index maintenance script

Code Examples

Example 1: PostgreSQL Index Advisor with pg_stat_statements

-- Enable pg_stat_statements extension for query tracking
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Configure extended statistics
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
SELECT pg_reload_conf();

-- View most expensive queries without proper indexes
CREATE OR REPLACE VIEW slow_queries_needing_indexes AS
SELECT
    queryid,
    LEFT(query, 100) AS query_snippet,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    stddev_exec_time,
…

# scripts/index_advisor.py - Comprehensive Index Analysis Tool
import psycopg2
from psycopg2.extras import DictCursor
import re
import logging
from typing import List, Dict, Tuple, Optional
from dataclasses import dataclass, asdict
from collections import defaultdict

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@dataclass
class IndexRecommendation:
    """Represents an index recommendation with impact analysis."""
    table_name: str
    recommended_index: str
    reason: str
…

Example 2: MySQL Index Advisor with Performance Schema

-- Enable performance schema for query analysis
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

-- Identify slow queries needing indexes
CREATE OR REPLACE VIEW slow_queries_analysis AS
SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS executions,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_time_ms,
    ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_time_ms,
    SUM_ROWS_EXAMINED AS total_rows_examined,
…