Performanceintermediate

Index Selection

Finding indexes you need, removing the ones you do not, and judging both from usage data rather than intuition.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

Indexes are a trade: faster matching reads, slower writes, more memory. Reviewing them periodically keeps the trade favourable.

Missing Indexes

The signal is always the same: rows read greatly exceeding rows returned.

-- PostgreSQL: tables where sequential scans dominate and the table is not small.
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       seq_tup_read / nullif(seq_scan, 0) AS avg_rows_per_seq_scan,
       n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND n_live_tup > 100000
ORDER BY seq_tup_read DESC LIMIT 20;

-- MySQL: statements doing full scans, ranked by their cost.
SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;

// MongoDB: queries whose plan was a collection scan.
db.system.profile.find({ planSummary: "COLLSCAN" }).sort({ ts: -1 }).limit(10);

Design the index from the query's predicates: equality columns first, then range, then sort. See Index Design.

Unused Indexes

Every index costs write throughput, memory and maintenance. Unused ones cost all of that for nothing.

-- PostgreSQL: never scanned, excluding those backing constraints.
SELECT s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- MySQL
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

// MongoDB
db.orders.aggregate([{ $indexStats: {} }]);

Statistics reset, and some indexes are used rarely by design

Index usage counters reset on restart and on an explicit statistics reset. An index serving a month-end report shows zero scans for 29 days. Before dropping anything, confirm the statistics cover a full business cycle, and check whether the index enforces a constraint.

Where the engine supports it, make the index invisible or disabled first — the change is instantly reversible, unlike a drop on a large table.

ALTER TABLE orders ALTER INDEX orders_status_idx INVISIBLE;   -- MySQL 8.0

Redundant indexes

An index on (a) is redundant when (a, b) exists, because the composite serves every query the single-column one does. sys.schema_redundant_indexes finds these in MySQL; in PostgreSQL, compare index definitions for shared prefixes.

Reviewing on a schedule

Index sets drift: someone adds one for a query that was later rewritten, and nothing removes it. Review quarterly:

List indexes by size, with their scan counts.
Confirm statistics cover a full business cycle.
Mark candidates invisible or disabled where possible.
Wait a cycle, then drop what nothing missed.
Re-check the missing-index signals afterwards, since removing an index can shift plans.