Queries that drag

N+1 and indexes, on measured numbers

What it is

Two reasons a backend starts to drag, and the AI reproduces both readily: extra queries inside a loop, and missing indexes.

The treacherous part is that on small data neither is noticeable. In development with ten users everything flies; at two hundred it starts to lag; at thousands it stops. The code never changed.

What it looks like in code

The N+1 query

The classic. Take the users, then for each one their subscriptions:

users = conn.execute("SELECT id, name FROM users").fetchall()
for u in users:
    conn.execute("SELECT city FROM subs WHERE user_id = ?", (u["id"],))

It looks innocent. In fact it is one query plus one per user — hence the name.

The same thing in one query:

conn.execute("""
    SELECT u.name, s.city
    FROM users u
    LEFT JOIN subs s ON s.user_id = u.id
""")

Measured on 200 users and 2000 subscriptions:

N+1:  201 queries, 12.5 ms
JOIN:   1 query,    1.9 ms, rows 2000

Six times faster, and that is on a local in-memory database. If the database is on another server, every query is also a trip across the network: two hundred trips instead of one. There the difference will not be sixfold but hundredfold.

Indexes

An index is a service structure that lets the database find rows without walking all of them.

CREATE INDEX idx_subs_user ON subs(user_id);

The same set of queries before and after:

without index: 13.0 ms
with index:     1.2 ms     — 10.5 times faster

One line in the schema, a tenfold speed-up. And an index is not free: it takes space and slows writes slightly. So they go not on everything but where things are searched for.

The rule: an index is needed on the fields that are searched or joined on — whatever appears in WHERE and in ON. In our case subs.user_id is in both.

How to see the problem

SQL has a built-in way of asking the database how it intends to run a query:

EXPLAIN QUERY PLAN SELECT city FROM subs WHERE user_id = 7;

SEARCH subs USING INDEX idx_subs_user (user_id=?)

SEARCH ... USING INDEX means the index is being used. If the answer is SCAN subs, the database is walking the whole table. In PostgreSQL the same thing is called EXPLAIN ANALYZE and it also prints the real time.

What to tell the AI

There is a database query inside a loop here. Rewrite it as
one query with a JOIN. The result must be the same: a list of
users, each with their cities, including users with no
subscriptions.

That last sentence matters: without it an INNER JOIN will lose the users who have no subscriptions (article 7.5).

Here is the table schema and the queries that run most often.
Which indexes are worth adding and why? For each one, which
query it speeds up.

How many database queries does this handler make per single
HTTP request? Count them.

The last one is a good check: an answer of "one" or "two" is fine, "depends on the number of users" means N+1.

What to look for

A query inside a loop. The main sign. Look for for and execute near each other.

A query inside a function that is called in a loop. The same thing one level deeper and therefore less visible. The sign: a function like get_cities(user) called while walking a list.

An ORM doing lazy queries. In the code it is a field access; in the database it is a query. for u in users: print(u.subs) looks like working with memory and is N+1.

No indexes at all. Apart from the PRIMARY KEY, which is created automatically. If the schema has not a single CREATE INDEX while searches by field do happen, they are needed.

Indexes on everything. The opposite extreme: every index slows writes and takes space.

"But it works." On your twenty test records it does. Check on a volume close to the real one.

Cheat sheet