An endpoint that feels instant with a hundred test rows can turn into a multi-second request once a Postgres table holds real data. The cause is almost always the same: a missing index. This guide explains why that happens and how to find the one you're missing.
In this article
The fix is usually cheap, often a single index, but only once you know which one and why it was missing. Guessing wastes time. A short diagnostic step points straight at the cause.
Key takeaways
- Postgres indexes primary keys and unique constraints automatically, but not foreign key columns.
- A missing index on a foreign key forces a full table scan on every join or filter that uses it.
- A query that filters and sorts on two columns needs one composite index in the right order, not two separate indexes.
EXPLAIN ANALYZEshows you the real cause in about two minutes, so you don't have to guess.
What is a database index?
An index is when the database keeps a separate, sorted structure for a column, so it can jump straight to matching rows instead of reading the whole table. Without one, Postgres checks every row in turn, called a sequential scan. That's fine on a small table and slow on a large one.
Why foreign keys don't get an index automatically
Postgres automatically creates an index for a primary key and for any column with a unique constraint. It doesn't do the same for a plain foreign key column. For example, an order.customer_id column that references the customers table gets no index unless you add one yourself.
This matters because every join or filter on that column runs a sequential scan by default. On a table with a few hundred rows, that's invisible. On a table with a few hundred thousand rows, it can become the entire latency budget of the request.
Composite indexes need to match the query
A single-column index doesn't help every query. If a query filters on status and orders by created_at, it needs a composite index on (status, created_at), in that order. Two separate single-column indexes don't combine the way you might expect.
When the index doesn't match the query shape, the planner falls back to scanning far more rows than necessary, even if an index exists on each column individually.
How to find the missing index
Run the actual query through EXPLAIN ANALYZE. Look for a Seq Scan on a table with a meaningful row count, especially one sitting inside a nested loop. That's the real signal, not a guess about which endpoint feels slow.
We recommend making this a two-minute check before any data-heavy endpoint goes live. It's cheap, and it tells you exactly where to add the index instead of guessing.
The opposite mistake: too many indexes
Indexing isn't free. Adding an index for every column that appears in a WHERE clause speeds up reads but slows down writes and adds storage cost. Add indexes deliberately, based on the queries your application actually runs, not as a reflex applied to every column a query touches.
Common mistakes
- Assuming foreign keys are indexed. Postgres only indexes primary keys and unique constraints automatically.
- Adding single-column indexes for a multi-column query. A query that filters and sorts on two columns needs one composite index, not two separate ones.
- Guessing which endpoint is slow. Run
EXPLAIN ANALYZEinstead of guessing from feel. - Indexing every column. Each index adds write cost and storage, so add them based on real query patterns.
FAQ
Does Postgres index foreign key columns automatically?
No. It indexes primary keys and unique constraints automatically, but a plain foreign key column needs its own index, added explicitly.
How do I know which index is missing?
Run the slow query through EXPLAIN ANALYZE and look for a Seq Scan on a table with a meaningful row count.
The bottom line
Most slow endpoints come down to the same cause: a missing or mismatched index, not a fundamentally slow database. Check whether your foreign keys are indexed, make sure composite indexes match how your queries actually filter and sort, and run EXPLAIN ANALYZE before guessing.
Database performance work like this is often one of the fastest fixes available to a slow product. The same connection and query patterns matter just as much once real traffic shows up.
Need help diagnosing a slow endpoint? Talk to our team.
