index-advisor

name	index-advisor
description	Analyze query patterns and recommend optimal database indexes
shortcut	inde

Database 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,
    ROUND((100.0 * total_exec_time / SUM(total_exec_time) OVER ()), 2) AS pct_total_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
  AND mean_exec_time > 100  -- Queries averaging >100ms
ORDER BY total_exec_time DESC
LIMIT 50;

-- Identify missing indexes by analyzing sequential scans
CREATE OR REPLACE  tables_needing_indexes 

    schemaname,
    tablename,
    seq_scan  sequential_scans,
    seq_tup_read  rows_read_sequentially,
    idx_scan  index_scans,
    idx_tup_fetch  rows_fetched_via_index,
    pg_size_pretty(pg_relation_size(schemanametablename))  table_size,
    
         seq_scan    ROUND(  seq_scan  (seq_scan  (idx_scan, )), )
         
      pct_sequential_scans
 pg_stat_user_tables
 seq_scan    
   seq_tup_read    
  seq_tup_read ;


  REPLACE  unused_indexes 

    schemaname,
    tablename,
    indexname,
    idx_scan  index_scans,
    idx_tup_read  tuples_read,
    idx_tup_fetch  tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid))  index_size,
    pg_get_indexdef(indexrelid)  index_definition
 pg_stat_user_indexes
 idx_scan    
   indexname     
   indexname     
  pg_relation_size(indexrelid) ;


  REPLACE  index_bloat_analysis 

    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid))  index_size,
    pg_size_pretty(pg_relation_size(tablename::regclass))  table_size,
    ROUND(  pg_relation_size(indexrelid)  (pg_relation_size(tablename::regclass), ), )  index_to_table_ratio,
    
         pg_relation_size(indexrelid)  pg_relation_size(tablename::regclass)  
         
         pg_relation_size(indexrelid)  pg_relation_size(tablename::regclass)  
         
         
      bloat_status
 pg_stat_user_indexes
 pg_relation_size(indexrelid)        
  pg_relation_size(indexrelid) ;

# 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
    affected_queries: List[str]
    estimated_speedup: str
    storage_cost_mb: float
    priority: str  # 'high', 'medium', 'low'

    def to_dict(self) -> dict:
        return asdict(self)

class PostgreSQLIndexAdvisor:
    """Analyze queries and recommend optimal indexes."""

    def __init__(self, connection_string: str):
        self.conn_string = connection_string

    def connect(self):
         psycopg2.connect(.conn_string, cursor_factory=DictCursor)

     () -> []:
        
        conn = .connect()

        :
             conn.cursor()  cur:
                cur.execute(, (min_duration_ms,))

                 [(row)  row  cur.fetchall()]

        :
            conn.close()

     () -> [[, ]]:
        
        columns = []

        
        where_pattern = 
        matches = re.finditer(where_pattern, query, re.IGNORECASE)

           matches:
            table = .group()
            column = .group()
            columns.append((table, column))

        
        join_pattern = 
        matches = re.finditer(join_pattern, query, re.IGNORECASE)

           matches:
            table = .group()
            column = .group()
            columns.append((table, column))

         columns

     () -> :
        
        conn = .connect()

        :
             conn.cursor()  cur:
                cur.execute(, (table, ))

                result = cur.fetchone()
                 result[]  result  

        :
            conn.close()

     () -> [IndexRecommendation]:
        
        recommendations = []

        
        slow_queries = .analyze_slow_queries()
        logger.info()

        
        column_usage = defaultdict(: {: , : [], : })

         query_info  slow_queries:
            query = query_info[]
            total_time = (query_info[])

            
            columns = .extract_where_columns(query)

             table, column  columns:
                
                  .check_existing_indexes(table, column):
                    key = 
                    column_usage[key][] += 
                    column_usage[key][].append(query[:])
                    column_usage[key][] += total_time

        
         key, usage  column_usage.items():
            table, column = key.split()

            
             usage[] > :  
                priority = 
                speedup = 
             usage[] > :  
                priority = 
                speedup = 
            :
                priority = 
                speedup = 

            
            storage_cost = .estimate_index_size(table)

            recommendation = IndexRecommendation(
                table_name=table,
                recommended_index=,
                reason=,
                affected_queries=usage[][:],  
                estimated_speedup=speedup,
                storage_cost_mb=storage_cost,
                priority=priority
            )

            recommendations.append(recommendation)

        
        recommendations.sort(
            key= r: (
                {: , : , : }[r.priority],
                -(  _  r.affected_queries)
            )
        )

         recommendations

     () -> :
        
        conn = .connect()

        :
             conn.cursor()  cur:
                cur.execute(, (table, table))

                result = cur.fetchone()
                 result:
                    
                     (result[] * , )
                   

         Exception  e:
            logger.warning()
             
        :
            conn.close()

     () -> []:
        
        conn = .connect()

        :
             conn.cursor()  cur:
                cur.execute()

                 [(row)  row  cur.fetchall()]

        :
            conn.close()

     ():
        
        logger.info()

        
        recommendations = .generate_recommendations()

         recommendations:
            logger.info()

             i, rec  (recommendations, ):
                logger.info()
                logger.info()
                logger.info()
                logger.info()
                logger.info()
                logger.info()
                logger.info()

        
        unused = .find_unused_indexes()

         unused:
            logger.info()

             idx  unused:
                logger.info()
                logger.info()

        logger.info()
        logger.info()
        logger.info()
        logger.info()

     () -> :
        
           size_str:
             (size_str.replace(, ).replace(, )) * 
           size_str:
             (size_str.replace(, ).replace(, ))
           size_str:
             (size_str.replace(, ).replace(, )) / 
         


 __name__ == :
    advisor = PostgreSQLIndexAdvisor(
        
    )

    advisor.generate_report()

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,
    SUM_ROWS_SENT AS total_rows_sent,
    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
  AND SCHEMA_NAME NOT IN (, , )
   AVG_TIMER_WAIT    
  SUM_TIMER_WAIT 
LIMIT ;



    object_schema  database_name,
    object_name  table_name,
    count_read  select_count,
    count_fetch  rows_fetched,
    ROUND(count_fetch  (count_read, ), )  avg_rows_per_select
 performance_schema.table_io_waits_summary_by_table
 object_schema   (, , )
   count_read  
  count_fetch ;



    t1.TABLE_SCHEMA  database_name,
    t1.TABLE_NAME  table_name,
    t1.INDEX_NAME  index1,
    t2.INDEX_NAME  index2,
    GROUP_CONCAT(t1.COLUMN_NAME   t1.SEQ_IN_INDEX)  columns
 information_schema.STATISTICS t1
 information_schema.STATISTICS t2
     t1.TABLE_SCHEMA  t2.TABLE_SCHEMA
     t1.TABLE_NAME  t2.TABLE_NAME
     t1.INDEX_NAME  t2.INDEX_NAME
     t1.COLUMN_NAME  t2.COLUMN_NAME
     t1.SEQ_IN_INDEX  t2.SEQ_IN_INDEX
 t1.TABLE_SCHEMA   (, , )
  t1.TABLE_SCHEMA, t1.TABLE_NAME, t1.INDEX_NAME, t2.INDEX_NAME
 ()  (
     ()
     information_schema.STATISTICS
     TABLE_SCHEMA  t1.TABLE_SCHEMA
       TABLE_NAME  t1.TABLE_NAME
       INDEX_NAME  t1.INDEX_NAME
);

// scripts/mysql-index-advisor.js
const mysql = require('mysql2/promise');

class MySQLIndexAdvisor {
    constructor(config) {
        this.pool = mysql.createPool({
            host: config.host,
            user: config.user,
            password: config.password,
            database: config.database,
            waitForConnections: true,
            connectionLimit: 10
        });
    }

    async analyzeTableIndexes(tableName) {
        const [rows] = await this.pool.query(`
            SELECT
                COLUMN_NAME,
                CARDINALITY,
                INDEX_NAME,
                SEQ_IN_INDEX,
                NON_UNIQUE
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = ?
            ORDER BY INDEX_NAME, SEQ_IN_INDEX
        `, [tableName]);

        return rows;
    }

    async findMissingIndexes() {
        // Analyze slow queries from performance schema
        const [slowQueries] = await this.pool.query(`
            SELECT
                DIGEST_TEXT AS query,
                COUNT_STAR AS executions,
                ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms,
                SUM_ROWS_EXAMINED AS total_rows_examined
            FROM performance_schema.events_statements_summary_by_digest
            WHERE AVG_TIMER_WAIT > 100000000
              AND SCHEMA_NAME = DATABASE()
            ORDER BY AVG_TIMER_WAIT DESC
            LIMIT 20
        `);

         recommendations = [];

         ( query  slowQueries) {
            
             whereMatch = query..();

             (whereMatch) {
                 column = whereMatch[];

                recommendations.({
                    : ,  
                    : column,
                    : query..(, ),
                    : query.,
                    : 
                });
            }
        }

         recommendations;
    }

     () {
        .();

         recommendations =  .();

        .();

        recommendations.( {
            .();
            .();
            .();
            .();
        });
    }
}


( () => {
     advisor =  ({
        : ,
        : ,
        : ,
        : 
    });

     advisor.();
})();

Error Handling

Error	Cause	Solution
"Index too large"	Index exceeds max key length	Use partial index or hash index for long columns
"Duplicate key violation"	Creating unique index on non-unique data	Check for duplicates before creating unique index
"Out of disk space"	Index creation requires temporary storage	Free up disk space or use CONCURRENTLY option
"Lock timeout"	Index creation blocking queries	Use CREATE INDEX CONCURRENTLY (PostgreSQL) or ALGORITHM=INPLACE (MySQL)
"Statistics out of date"	Old cardinality estimates	Run ANALYZE (PostgreSQL) or ANALYZE TABLE (MySQL)

Configuration Options

Index Types

• B-Tree: Default, good for equality and range queries
• Hash: Fast equality lookups (PostgreSQL 10+)
• GIN/GiST: Full-text search and JSON queries
• BRIN: Block range indexes for very large sequential tables

Index Options

• CONCURRENTLY: Create without blocking writes (PostgreSQL)
• ALGORITHM=INPLACE: Online index creation (MySQL)
• INCLUDE columns: Covering index (PostgreSQL 11+)
• WHERE clause: Partial index for filtered queries

Best Practices

DO:

• Create indexes on foreign key columns
• Use composite indexes for multi-column WHERE clauses
• Order composite index columns by selectivity (most selective first)
• Use covering indexes to avoid table lookups
• Create indexes CONCURRENTLY in production
• Monitor index usage with pg_stat_user_indexes

DON'T:

• Create indexes on every column "just in case"
• Index low-cardinality columns (boolean, enum with few values)
• Use functions in WHERE clauses on indexed columns
• Forget to ANALYZE after index creation
• Create redundant indexes (e.g., (a,b) and (a) both exist)

Performance Considerations

• Each index adds 10-30% write overhead (INSERT/UPDATE/DELETE)
• Indexes consume storage (typically 20-30% of table size)
• Too many indexes slow writes more than they speed reads
• Index-only scans are 3-10x faster than table lookups
• Covering indexes eliminate random I/O entirely
• Partial indexes reduce storage and maintenance overhead

Related Commands

• /sql-query-optimizer - Rewrite queries for better performance
• /database-partition-manager - Partition large tables for faster queries
• /database-health-monitor - Monitor index bloat and maintenance needs
• /database-backup-automator - Schedule REINDEX during maintenance windows

Version History

• v1.0.0 (2024-10): Initial implementation with PostgreSQL and MySQL support
• Planned v1.1.0: Add hypothetical index simulation and automated A/B testing