PostgreSQL doesn't index your foreign keys

PostgreSQL creates an index automatically for PRIMARY KEY and UNIQUE. For a foreign key, it doesn't. This surprises people because MySQL/InnoDB indexes FKs on its own, and anyone coming from there assumes Postgres does the same.

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    total NUMERIC(12,2) NOT NULL
);

In this schema, orders.customer_id has no index. What that costs:

Joins and lookups by customer (WHERE customer_id = ?) turn into a sequential scan over the orders table. At 10k rows nobody notices; at 10 million the endpoint that lists a customer's orders melts.
DELETE and UPDATE on the parent table get expensive: for every row deleted in customers, Postgres has to check whether a child points at it. Without an index, that's a full scan of orders per check. It's the classic "delete that locks up the table".

The parent-delete case is worse than it sounds

The FK check runs with the delete's row locks already held, so the full scan doesn't just make your statement slow, it stretches the window in which other writers wait behind it. A batch job doing DELETE FROM customers WHERE ... over a few thousand parents against an unindexed 50-million-row child table is hours of scanning, and the same trap fires on UPDATE customers SET id = ... (rare) and on ON DELETE CASCADE, where each cascaded delete needs the same lookup. The symptom in pg_stat_activity is a wall of sessions waiting on locks behind one innocent-looking delete.

The fix

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

In production, use CREATE INDEX CONCURRENTLY so you don't hold a write lock while it builds (it takes longer and can't run inside a transaction, but the table keeps serving writes).

When an index you already have covers it

A B-tree index serves any leftmost prefix of its columns. If the table already has:

CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at);

then customer_id lookups are covered and a dedicated single-column index is redundant, drop the idea, not the composite. The coverage does not work the other way around: an index on (created_at, customer_id) does not serve WHERE customer_id = ?, because the FK column isn't the prefix. When auditing, check the column order, not just the column list.

How to find the unindexed FKs in your database

SELECT c.conrelid::regclass AS table, a.attname AS column
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = i.indkey[0]   -- leftmost column only: prefix rule
  );

Note the indkey[0]: an FK column buried in the middle of some index doesn't count as covered, only a leftmost position does.

Not every FK needs one

If the column never shows up in a join, a filter or a cascading delete, the index just costs you on writes: every INSERT and UPDATE maintains it, and it competes for cache. Genuine examples exist, an audit-ish reference nobody queries by, a parent table with a handful of rows that never sees deletes. But that's the rare case, and the failure mode of the missing index (production melts under load) is much worse than the failure mode of the extra one (writes a little heavier). When in doubt, index it, and let a slow-write investigation remove it later with evidence in hand.

Schemint flags unindexed FKs straight from your CREATE TABLE, alongside the JPA Entity, the Flyway migration and the ER diagram.