Analyze database indexing strategies and recommendations. Part of the DevTools Surf developer suite. Browse more tools in the Database Tools collection.
Use Cases
Identify missing indexes causing slow queries in a production database
Detect unused indexes that waste storage and slow down writes
Optimize composite index column ordering for multi-condition queries
Analyze index coverage for common query patterns before a traffic spike
Tips
Paste your CREATE INDEX and EXPLAIN ANALYZE output together — the analyzer compares existing indexes against the query plan to identify unused or missing indexes
Check the 'index selectivity' panel: high-selectivity indexes (many distinct values, like user IDs) are worth adding; low-selectivity indexes (boolean, status with 3 values) rarely help
Use the composite index advisor: column order in a composite index matters — the leftmost prefix rule means the index must start with the most selective equality-comparison column
Fun Facts
B-tree indexes, used by PostgreSQL and MySQL by default, were invented by Rudolf Bayer and Edward McCreight at Boeing in 1972. The B-tree allows O(log n) search, insert, and delete — the same complexity regardless of data size.
PostgreSQL's EXPLAIN ANALYZE output shows the query plan and actual execution times. The 'rows' estimate vs 'actual rows' discrepancy is the most important diagnostic signal — large discrepancies indicate stale statistics needing a VACUUM ANALYZE.
A properly placed index can improve query performance from O(n) (full table scan) to O(log n) — a 100,000 row table scan takes 100,000 operations; an indexed lookup takes about 17 (log2(100,000) ≈ 17). The difference is often the margin between a 0.1ms and 200ms query.
FAQ
Should I index every foreign key column?
Yes, in most cases. Foreign keys without indexes cause full table scans during JOIN operations and cascading deletes/updates. PostgreSQL automatically creates indexes on primary keys but not foreign keys — add them manually.
What is an index-only scan and when is it possible?
An index-only scan retrieves all needed data from the index itself without touching the table heap. This is only possible when the index covers all columns in the SELECT clause. Designing 'covering indexes' is a key performance optimization technique.
How do I know if my index is being used?
Run EXPLAIN (ANALYZE, BUFFERS) on the query. Look for 'Index Scan' or 'Index Only Scan' in the plan. 'Seq Scan' means the index is not being used. pg_stat_user_indexes shows index usage statistics over time for unused index identification.
