It's one of the most common causes of production slowdown, and it almost always has the same shape: an endpoint that felt instant in development, with a few hundred seed rows, turns into a multi-second request once the underlying table has real volume in it. The fix, more often than not, is a single missing index, but knowing which one, and why it was missing, is worth understanding rather than guessing at.
The mistake that catches people off guard: Postgres does not automatically index foreign key columns. It indexes primary keys and unique constraints, but a plain foreign key column (`order.customer_id`, say) gets no index unless one is added explicitly. That means every join or filter on that column runs a sequential scan by default. On a small table that's invisible. On a table with a few hundred thousand rows, it can become the entire latency budget of the request.
A related version of the same problem is a composite query with no composite index to match it. A query that filters on `status` and orders by `created_at` needs an index on `(status, created_at)`, in that order; separate single-column indexes on each field don't get combined the way people often expect, and the planner falls back to scanning far more rows than necessary.
The diagnostic step is the same every time and takes about two minutes: run the actual query through `EXPLAIN ANALYZE` and look for a `Seq Scan` on a table with meaningful row count, especially one sitting inside a nested loop. That's the real signal, not a guess about which endpoint feels slow. It's a cheap check worth making standard practice before any data-heavy endpoint goes live.
The failure mode on the other end is over-indexing: adding an index for every column that appears in a `WHERE` clause somewhere. Every index speeds up reads but slows down writes and adds storage cost, so it's a tradeoff worth making deliberately, based on actual query patterns, not a reflex applied everywhere a query touches a column.
