PostgreSQL Performance Tuning: Indexes, EXPLAIN and the N+1 Trap

When an app gets slow, the database is usually the culprit — and the cause is almost always a missing index or an N+1 query. Here's how to find and fix both.

Read the Query Plan

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

A Seq Scan over a large table is your red flag — Postgres is reading every row. An Index Scan means it's using an index. Aim to turn the former into the latter.

Add the Right Index

-- Index the columns you filter and join on
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index for multi-column filters (order matters!)
CREATE INDEX idx_orders_cust_date ON orders(customer_id, created_at);

Kill the N+1 Query

Looping over results and querying inside the loop runs hundreds of queries. Fetch related data in one go with a JOIN or your ORM's eager loading (include, with, selectinload).