The Missing Index on a JOIN: The Beginner Fix for Slow PostgreSQL Queries

Overview

A query joining orders to order_items was instant in development and now takes seconds in production. The tables grew, nothing else changed, and the plan shows a sequential scan on the child table. A very common cause is a foreign key column with no index.

Many developers come from databases or ORMs where this seems to be handled for them. In PostgreSQL it is not.

1. Foreign Keys Do Not Create Indexes

When you declare REFERENCES in PostgreSQL, the referenced column must be unique (usually the primary key, which is indexed). The referencing column, the one holding the foreign key, gets no index automatically.

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL
);

CREATE TABLE order_items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders (id),
    sku text NOT NULL
);
-- order_items.order_id has NO index here

Now SELECT ... FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.id = 42 must find matching items. Without an index on order_items.order_id, the planner may read the entire table to do it.

2. Confirm It in the Plan

Run the slow query with EXPLAIN (ANALYZE, BUFFERS) and look at the child table.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, i.sku
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.customer_id = 7;

Warning signs are a Seq Scan on order_items with a large Rows Removed by Filter count, or a nested loop that repeats a sequential scan for each outer row. For a small table a sequential scan is perfectly fine, so judge by row counts and timing, not by the node name alone.

3. Find Foreign Keys Without a Supporting Index

Rather than checking one query at a time, list every foreign key whose columns are not the leading columns of some index. This catalog query is a reasonable starting point for single-column and simple multi-column keys.

SELECT c.conrelid::regclass AS table_name,
       c.conname            AS fk_name,
       pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] <@ c.conkey
  )
ORDER BY 1, 2;

Treat the output as candidates, not orders. Review each one against real queries before adding anything, because every index has a cost (see the related post on indexes versus write speed).

4. Add the Index Without Blocking Writes

A plain CREATE INDEX blocks writes to the table while it builds. On a live table use CONCURRENTLY.

CREATE INDEX CONCURRENTLY idx_order_items_order_id
    ON order_items (order_id);

Things to know:

• It cannot run inside a transaction block. Many migration tools wrap migrations in a transaction by default, so you may need to disable that for this migration. Check your tool's documentation.
• It takes longer than a normal build and does more table scans.
• If it fails, it can leave an invalid index behind. Check with \d order_items or query pg_index.indisvalid, then drop the invalid index and retry.

DROP INDEX CONCURRENTLY IF EXISTS idx_order_items_order_id;

5. Verify the Improvement

Run the same EXPLAIN (ANALYZE, BUFFERS) again. You want to see an Index Scan or Bitmap Heap Scan on the new index and a drop in both execution time and buffers read. If the planner still chooses a sequential scan, run ANALYZE order_items; to refresh statistics, and check whether the query actually returns a large fraction of the table, in which case a sequential scan may genuinely be the best plan.

6. Also Consider Deletes and Updates

The index helps more than joins. When you delete or update a row in the parent table, PostgreSQL checks the child table for referencing rows. Without an index on the foreign key column, each such check can scan the whole child table, which makes parent deletes surprisingly slow. This is a second reason to index foreign keys that sit on large tables.

Conclusion

If a JOIN got slower as data grew, check whether the foreign key column is indexed. Find candidates from the catalog, confirm with the plan, build with CREATE INDEX CONCURRENTLY, and measure again.

If you have queries that stay slow after basic indexing and want help reading plans and designing indexes, I can help. Contact me.

Additional Resources

• PostgreSQL CREATE INDEX documentation
• PostgreSQL constraints documentation
• PostgreSQL using EXPLAIN