How to speed up a slow SQL query, in order

Someone says a page is slow. You find the query. Now what?

Most people start by adding an index, because that is the advice everyone remembers. Sometimes it works. Often it adds write cost for no read benefit, because the index was not the problem.

Here is the order that actually finds the cause.

1. Measure it properly

Before changing anything, get the real plan with real timings:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

ANALYZE runs the query and reports actual times, not estimates. BUFFERS tells you how much came from cache and how much from disk.

Run it twice. The first run may be reading cold from disk and will mislead you about steady-state behaviour.

2. Read the plan from the inside out

Plans are trees. The deepest nodes run first. Read bottom-up.

Three things to look for, in order of how often they are the answer:

A large gap between estimated and actual rows. If the planner expects 10 rows and gets 400,000, it chose a strategy for the wrong problem. Everything above that node is built on a bad assumption. Usually the fix is stale statistics, so run ANALYZE your_table and look again.

A sequential scan on a large table with a selective filter. Reading every row to return a few. This is the case where an index genuinely helps.

A nested loop over a large outer set. Fine for a handful of rows, catastrophic for a hundred thousand. Often another symptom of the bad row estimate above.

3. Check for N+1 before you touch the database

If the slow thing is a page rather than a single query, count the queries first. An ORM iterating a collection and lazily loading a relation for each row produces hundreds of fast queries that add up to a slow page. No amount of index tuning fixes that.

In SQLAlchemy:

# N+1: one query for orders, then one per order for its customer
orders = session.query(Order).all()
for o in orders:
    print(o.customer.name)

# One query, or two
from sqlalchemy.orm import selectinload
orders = session.query(Order).options(selectinload(Order.customer)).all()

selectinload issues a second query with an IN clause. joinedload does a join. Use selectinload for collections, where a join would multiply your rows, and joinedload for a single related object.

Whenever I have been handed something described as a database performance problem, this has been the cause about half the time.

4. Then consider an index

Now that you know the query is doing real work on real rows, index the columns in your WHERE, JOIN, and ORDER BY clauses.

Two things worth knowing:

Column order in a composite index matters. An index on (tenant_id, created_at) serves a filter on tenant_id alone, and a filter on both. It does not help a filter on created_at alone. Put the equality columns first and the range column last.

A function on the column disables the index.

-- index on created_at is not used
WHERE DATE(created_at) = '2026-08-04'

-- it is
WHERE created_at >= '2026-08-04' AND created_at < '2026-08-05'

Same for LOWER(email) = ... unless you have an expression index on LOWER(email).

5. Ask for less

The cheapest query is the one that returns less.

• SELECT * on a wide table drags back columns nobody reads, including the large text ones. Name the columns.
• Paginate. If the interface shows 25 rows, do not fetch 50,000 and slice in application code.
• Beware OFFSET on deep pages. OFFSET 100000 makes the database walk and discard 100,000 rows. Keyset pagination is better: WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 25.

6. Cache, last

Caching is the right answer when the query is already efficient and simply expensive, and the data can tolerate being slightly stale.

It is the wrong answer when it is being used to hide a query you have not understood. That version comes back, because now you have the original problem plus an invalidation bug.

The order matters

At Infosys I spent a lot of time on production incidents in a large logistics platform, and the pattern held: measure, read the plan, check for N+1, then index, then reduce, then cache. Working in that order found causes. Starting with an index found symptoms.