When a query is slow, the common response is to add an index to whatever column appears in the WHERE clause and hope. Sometimes that works. Often it adds write cost and storage for no read benefit, because the planner was never going to use it. The database will tell you what it is actually doing if you ask, and asking takes one keyword.
Run EXPLAIN ANALYZE on the query - the ANALYZE part matters, because plain EXPLAIN shows the plan the optimiser intends while ANALYZE actually executes it and reports real timings and row counts. The single most useful thing in that output is the comparison between estimated rows and actual rows. When the planner expects fifty and gets fifty thousand, it chose a strategy suited to fifty, and that mismatch - not the strategy - is your problem. It usually means statistics are stale, and running ANALYZE on the table fixes more slow queries than any index.
The scan types tell you the rest. A sequential scan reads the whole table, which is correct and fast for small tables or when you genuinely want most rows, and disastrous on a large table returning a few. An index scan finds rows via the index then fetches each from the table. An index-only scan answers entirely from the index without touching the table at all, which is the fastest option and the reason covering indexes - indexes that include the columns you select, not just the ones you filter on - are worth knowing about.
Column order in a composite index is the thing most often got wrong. An index on (customer_id, created_at) serves a query filtering on customer_id, and one filtering on both. It does not serve a query filtering only on created_at, because the index is sorted by customer first. The rule is equality columns first, then the range or sort column. Getting this backwards produces an index that exists, looks reasonable, and is never used.
Several common patterns silently prevent index use. Wrapping the column in a function - WHERE LOWER(email) = something - cannot use an ordinary index on email, though an expression index on LOWER(email) will. Leading wildcards in LIKE cannot use a normal index. Implicit type casts between a column and a parameter of a different type will quietly disable it too. All of these look fine in the query and appear in the plan as a sequential scan you cannot explain.
Finally, remember the cost side. Every index makes writes slower and consumes storage, and unused indexes are pure overhead. Both PostgreSQL and MySQL expose statistics on index usage - check periodically for indexes with zero scans and drop them. A table with fourteen indexes, half of them unused, is a write-performance problem that started as a series of individually reasonable attempts to fix a read.
