Index a foreign key column Postgres never indexed for you
orders.customer_id references customers.id — the foreign key is already in place and enforced. What surprises a lot of people: Postgres does not automatically create an index on the referencing column of a foreign key (only on the referenced side, because a primary key already implies one). Every lookup of "this customer's orders" is currently a full scan of orders.
Create an index on orders.customer_id.
Schema
| id | INT |
| name | TEXT |
| id | INT |
| customer_id | INT |
| amount | NUMERIC |
Example input
Why
A PRIMARY KEY builds its own unique index automatically, which is why customers.id was already fast to look up. A plain FOREIGN KEY gets no such index — Postgres only guarantees the referenced side (customers.id) has one, because that's what the constraint needs to check on every insert. Nothing requires the referencing side (orders.customer_id) to be indexed, even though "find every order for this customer" is one of the most common queries a schema like this will ever run. Missing indexes on FK columns is a genuinely common real-world gap, not a contrived teaching example — it's also the exact reason DELETE FROM customers WHERE id = ... can be slow on a large orders table even after the cascade from the previous chapter's foreign-key work: without this index, checking "does anything in orders still reference this customer" is a full scan.
Stuck? Ask the coach for a nudge that won't give the answer away, or reveal the reference solution and a short walkthrough of why it works.