This table has fourteen indexes and writes have got slower. How do you work out which ones to drop?

What fourteen indexes cost on the write path

Before choosing which to remove, be precise about the mechanism you are trying to relieve, because the size of the effect differs sharply by workload. An INSERT has to place an entry in every index on the table, so insert cost scales roughly with index count. A DELETE similarly leaves work in every index for the vacuum or purge machinery to clean up later. An UPDATE is where the interesting variation lives.

In PostgreSQL an update writes a new row version, and if no indexed column changed and the new version fits on the same page, the engine can use a heap-only tuple update, which leaves the indexes untouched and points existing index entries at the new version through the page. Index every column and you erode that possibility: an update that touches any indexed column must insert an entry into every index, not only the one on the changed column. So the marginal cost of the fourteenth index is not one fourteenth of the maintenance, it can be the difference between a cheap in-page update and one that writes fourteen index entries and more log. That is why an index added to a hot, frequently-updated column is disproportionately expensive, and it is the single most useful thing to be able to say in this conversation.

The costs that are not on the write path matter too and are often what actually hurts. Indexes compete for the same shared buffers as your data, so a table with fourteen of them evicts more useful pages. Vacuum has to scan every index of a table to remove its dead pointers, so index count directly extends maintenance duration on the biggest tables. In InnoDB, every secondary index entry carries the primary key as its row pointer, which means a wide primary key inflates all fourteen at once — a detail worth naming because the fix there is sometimes the primary key rather than the indexes.

Read the usage counters, then read them sceptically

PostgreSQL keeps per-index scan counts, and they are the right starting point.

SELECT s.indexrelname,
       s.idx_scan,                                    -- scans since the last statistics reset
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       i.indisunique                                  -- true means it may be enforcing a constraint
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.relname = 'orders'
ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;

The number is meaningless without knowing the window it covers, so the companion query is the one that tells you when the counters were cleared.

SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

If the answer is last Tuesday, a zero scan count says nothing about the monthly billing job, the quarterly export, or the annual audit query, and dropping on that basis will be discovered by whoever runs the report. Two other properties of these counters catch people out. They are per-instance, so if read traffic is served by replicas then the primary's counters describe only the write workload and the indexes serving your heaviest reads will look idle. And a restart from a crash, or a manual reset, resets the window without announcing it.

The equivalent in MySQL is the Performance Schema's index usage summary, subject to the same caveat that it counts since the server last started.

The indexes that look idle but are not free to drop

Several categories will show zero or near-zero scans and still must stay.

A unique index is doing a job on the write path regardless of reads: it is the enforcement mechanism for a uniqueness rule, and the scan counter does not reflect the duplicate checks. If it backs a declared constraint you cannot drop the index directly at all in PostgreSQL — you drop the constraint, which is a different and more consequential decision.

An index on the referencing side of a foreign key is scanned when the referenced row is updated or deleted, and in some engines that lookup does not appear as a normal index scan. Remove it and deletes on the parent table degrade to a full scan of the child, which is a failure that shows up as a mysterious slow DELETE weeks later.

An index may also be feeding the planner rather than a predicate — supplying ordering for a query with ORDER BY ... LIMIT, or supporting a merge join. It shows as a scan, but a low count on a query that runs rarely and matters enormously is exactly the case where the counter misleads.

Redundancy is about leading columns

The best drops are the ones that lose you nothing, and those come from overlap rather than from disuse. In a B-tree, an index on (customer_id) is subsumed by an index on (customer_id, created_at), because the second can serve any predicate the first can, using its leading column. The reverse is not true. So the mechanical pass is to group the indexes on the table by their leading column and look for any whose column list is a strict prefix of another's.

Two qualifications keep this from being blind. The narrower index is physically smaller, so it can win where an index-only scan matters or where the wider one no longer fits comfortably in cache, and the difference is occasionally worth keeping both. And a prefix relationship only holds when the shared columns are in the same order and sorted the same way; (a, b) and (a, b DESC) are not interchangeable for a query that wants one ordering and a LIMIT.

Duplicates, as opposed to prefixes, are pure profit and are surprisingly common. Migration tooling that adds an index and a unique constraint on the same column, or two developers adding the same index under different names, produces two structures with identical definitions that both get maintained on every write.

Dropping it reversibly

Do not go straight from a counter to a DROP. Take the candidate index, find the statements that plausibly depend on it — the statement statistics view is where those live, and you want the full query text rather than only its identifier — and check their plans. Then remove it in a way you can undo.

MySQL makes this easy: an invisible index is still maintained but hidden from the optimiser, so you can test the workload without it and reverse the change instantly. PostgreSQL has no equivalent, so the reversible options are to drop inside a transaction that you roll back after inspecting plans, accepting that this holds an exclusive lock for the duration, or to drop for real with DROP INDEX CONCURRENTLY and be ready to rebuild with CREATE INDEX CONCURRENTLY. The concurrent forms are what keep this a routine operation rather than an outage, and the reason to prefer them is that the plain forms take a lock that queues behind and in front of every query on the table.

Whichever route you take, capture the exact definition first, and drop one index at a time with a period of observation between, so that when latency moves you know which change moved it.

The valuable drops are the redundant ones and the duplicates, not merely the ones with a low scan count. A zero in the usage counter is a question about the window it was measured over, and about whether that index is enforcing something rather than serving something.