Indexing problems rarely show up in development — the dataset is too small to matter. They show up eighteen months into production, when a query that used to take 20ms suddenly takes 4 seconds under real data volume.
The most common mistake we see is composite index column ordering that doesn't match query patterns. An index on (status, created_at) is nearly useless for a query that filters only on created_at, even though the column exists in the index.
The opposite mistake — over-indexing — is just as damaging. Every additional index slows down writes and consumes memory that could otherwise cache your hot data. We routinely find tables with a dozen indexes where three would cover every real query pattern.
Our standard practice is to review query plans against production-representative data before finalizing an indexing strategy, not against a development database with a thousand rows that tells you nothing about real-world performance.