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 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

  pg_stat_reset()

Instructions

1. 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

2. 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

3. 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

4. Recommend composite indexes for multi-column queries. Follow the equality-first, range-second ordering:

   • Place columns used with

     =

     operators first in the index
   • Place columns used with

     >

     ,

     <

     ,

     BETWEEN

     , or

     LIKE 'prefix%'

     last
   • Example:

     WHERE status = 'active' AND created_at > '2024-01-01'

     ->

     CREATE INDEX ON orders (status, created_at)

5. Identify unused indexes wasting write performance:

   • PostgreSQL:

     SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey' ORDER BY pg_relation_size(indexrelid) DESC

   • Indexes with zero scans over a representative period are candidates for removal (verify they are not used by foreign key constraints or unique enforcement)

6. Detect redundant indexes where one index is a prefix of another:

   • A single-column index on

     (customer_id)

     is redundant if a composite index on

     (customer_id, created_at)

     exists, because the composite index serves both single-column and multi-column queries
   • Generate DROP INDEX recommendations for the redundant subset indexes

7. Evaluate partial indexes for filtered queries. If a query always filters

   WHERE status = 'active'

   :

   • CREATE INDEX idx_orders_active ON orders (created_at) WHERE status = 'active'

   • Partial indexes are smaller and faster than full indexes when the filter eliminates most rows

8. Consider covering indexes (INCLUDE clause in PostgreSQL 11+) for index-only scans:

   • CREATE INDEX idx_orders_covering ON orders (customer_id, created_at) INCLUDE (total_amount, status)

   • The INCLUDE columns are stored in the index leaf pages, enabling index-only scans without heap access

9. Estimate the impact of each recommendation:

   • Index size:

     SELECT pg_size_pretty(pg_relation_size('index_name'))

     for existing similar indexes
   • Write overhead: each additional index adds approximately 5-15% write latency per INSERT/UPDATE
   • Read improvement: compare EXPLAIN plans with and without the proposed index

10. Generate a prioritized recommendations report with CREATE INDEX and DROP INDEX statements, estimated storage impact, expected query improvement, and write overhead trade-off analysis.

Output

• Missing index recommendations as ready-to-execute CREATE INDEX statements with CONCURRENTLY option
• Unused index report with DROP INDEX candidates and their storage savings
• Redundant index report identifying prefix-overlapping indexes
• Index usage statistics showing scan counts, tuple reads, and sizes for all indexes
• Impact analysis estimating read improvement vs. write overhead for each recommendation

Error Handling

pg_stat_statements

CREATE EXTENSION pg_stat_statements

shared_preload_libraries

CREATE INDEX

CREATE INDEX CONCURRENTLY

ANALYZE table_name

random_page_cost

pg_stat_reset()

Examples

Identifying a missing composite index for an API endpoint: The

/orders?customer_id=123&status=active

(id)

(customer_id)

(customer_id, status)

CREATE INDEX CONCURRENTLY idx_orders_customer_status ON orders (customer_id, status)

Cleaning up 8 unused indexes saving 12GB: Index usage analysis reveals 8 indexes with zero scans over 30 days, totaling 12GB of storage. After confirming none are used for FK enforcement or unique constraints, dropping them reduces write latency by 18% and frees disk space. Command:

DROP INDEX CONCURRENTLY idx_name

Replacing 3 single-column indexes with 1 composite covering index: Table has separate indexes on

(user_id)

(created_at)

(status)

(user_id, status, created_at) INCLUDE (amount)

Resources

• PostgreSQL index types: https://www.postgresql.org/docs/current/indexes.html
• Use The Index, Luke: https://use-the-index-luke.com/
• pg_stat_user_indexes: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW
• MySQL index optimization: https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html
• Dexter (automatic PostgreSQL index advisor): https://github.com/ankane/dexter