Query Optimization for Backend Engineers: Indexes, Execution Plans, and Real-World Tuning

Query Optimization for Backend Engineers matters because one slow statement can stall a whole API. When the plan reads too many rows, the request holds a connection until the pool fills. Then users retry, and the database sees even more load. You should treat a bad plan as an incident risk, not as a cleanup task for later.

What Query Tuning Actually Is

Query tuning is the work of making each SQL call touch less data and finish sooner. It is not a contest to write clever SQL. It is a check that the filter, the join, and the sort match an access path the engine can use.

The engine keeps stats about tables and indexes. Before it runs your statement, it prices a few plans. Then it picks the one with the lowest cost. If those stats are stale, the price is a lie, and the chosen plan can be the slow one.

An index helps when it matches the predicate you actually run. A wider index can also cover the query, so the engine skips the heap. However, each extra index slows writes and adds vacuum work. You pay that cost on every insert, not only on the read you hoped to speed up.

You should read the plan instead of guessing. The PostgreSQL EXPLAIN docs show how to print the chosen path. EXPLAIN ANALYZE runs the statement and adds real time. Use both on a copy of production data when you can, because a tiny staging table hides the cost.

Why It Fails in Production

A query fails in production when the plan that was fine on a small table scans a huge one. The data grew, but the index does not match the filter. As a result, one endpoint holds connections until callers time out.

I have seen a checkout path die on a single unindexed filter. The SQL was correct. Still, it read millions of rows to return a page of ten. Because the call sat inside the request, every extra user made the stall worse.

When you only test on a laptop copy, the planner looks smart. Row counts are tiny, so a scan is cheap. After you ship, the same plan is the outage.

The code did not change. The table did.

A nested loop that looks cheap can explode when the inner side is large. A hash join can spill to disk when work memory is small. Also, a sort on a wide text column can dominate the whole call even when the filter is selective.

Sometimes the SQL is fine and the app is not. If you load a parent and then one query per child, you have N+1 queries. Fix that shape before you tune a single statement. An index will not save a loop that runs a thousand times per page.

Slow queries also interact with the pool. When each call stays busy longer, new requests wait for a session. Read connection pooling next to the query time, because the user sees the wait, not the plan node.

How a Safe Change Is Built

A safe change has four steps. First, capture the slow statement and its plan. Next, match the predicate to an index you can justify.

Then, ship the index so it does not block writes for long. Finally, watch latency and write cost for a full day.

On PostgreSQL, CREATE INDEX CONCURRENTLY builds the index without a long write block. It still takes disk and CPU. If the build fails, you must drop the invalid index before you retry. Leave it in place and the next build will refuse to start.

Put the new index in your migration tool with a clear rollback note. Database migrations at scale need the same care as a schema change that rewrites a table. A concurrent build can still spike IO and push replica lag up while it runs.

The app should send a tight query. Select only the columns you use. Filter as early as you can. Avoid a function on the column you want to index, because that often blocks the index and forces a scan.

How to read a plan

Start at the node with the highest actual time. Then check rows removed by filter. If the engine reads far more rows than it returns, the index is the wrong shape or the predicate is not selective.

Also check the buffers line when you use EXPLAIN ANALYZE. A high shared-read count means the working set is not in cache. More RAM can help for a while. It will not fix a plan that reads the whole table on every call.

The PostgreSQL index guide lists B-tree, hash, GiST, and GIN. Most equality and range filters want a B-tree. Use GIN when you search inside arrays or full text. Do not add a special index type until the plan proves you need it.

Column order and covering

For a composite index, put the equality columns first and the range column last. If you filter on user id and status, then sort by created time, that order should match the index. A reversed order can still be used, but it often sorts again.

A covering index includes the columns you select, so the engine can answer from the index alone. That helps a hot read path. It also makes the index wider, so writes move more bytes. Add the extra columns only after you see heap fetches in the plan.

Trade-offs You Should Weigh

You should not add an index for every slow log line. Some queries are rare and cheap in total. Others need a product change, such as a smaller page or a precomputed column. The table below is the set of choices I use in design notes.

Compare a scan and an index when you write the design note. Also compare the write path. A table with twenty indexes can make a simple insert the new bottleneck. If the write rate is the limit, drop an index before you add one.

When read volume is the real limit, move some reads to read replicas. That does not repair a bad plan. It copies the same plan to more hosts. Tune the statement first, then add copies if the primary is still hot.

Pitfalls and Failure Modes

Most of these show up as a sudden latency jump, not as a red error. The query still returns the right rows. It just returns them too late. Watch the slow log and the plan, not only the error rate.

1. You index the wrong column order for a composite key.
2. You trust a plan from an empty staging database.
3. You wrap the column in a function and hide it from the index.
4. You select every column and drag wide rows over the network.
5. You use a deep offset and scan the skipped rows every time.
6. You ignore lock waits and call them slow queries.

A common mistake I have seen is a feature flag that adds OR branches. The planner then skips the index and scans. If you can split the query into two statements, do that. Two simple plans are often faster than one clever plan.

Also watch parameter types. A string compared with an int can force a cast. Then the index sits unused while the scan runs.

Bind the same type the column uses. Check the plan after you change the client type.

Deep pages are another trap. OFFSET 100000 still walks those rows before it returns the next page. Use a keyset page instead.

Pass the last seen created time and id, and filter with a range. The index can then stop after one page.

Stats go stale after a bulk load. If you just imported a large file, run analyze before you trust the plan. Otherwise the planner still thinks the table is small. The PostgreSQL performance tips call this out, and it still bites teams every quarter.

A Realistic Tuning Example

The statement below lists paid orders for one user, newest first. Before the index, the plan was a scan plus a sort. After the index, the engine can walk the index in order and stop at twenty rows. That is the win you want.

CREATE INDEX CONCURRENTLY IF NOT EXISTS
  orders_user_status_created_idx
ON orders (user_id, status, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE user_id = $1
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Still, check the buffers line. If you read far more blocks than rows, the predicate is not selective. Then tighten the filter or add a partial index for paid status only. A partial index stays small when most rows are not paid.

What to check before you ship

When you review a query change, print the plan on a realistic row count. Confirm the index name in the plan. Confirm the query does not cast the indexed column.

Also confirm the migration uses a concurrent build on a live table. A plain create index can block writes for the whole build. That is fine on a new empty table. It is not fine on the orders table at noon.

Performance, Scale, and Cost

Index builds on a large table can take hours. In an illustrative production range, a few hundred GB can mean a long night of disk read. Plan free disk, because the build needs room for the new index plus the old one until you are sure.

At scale, the cost is not only query time. It is also replica lag while the index ships, and CPU on the primary during the build. Run the build in a quiet window when you can.

Track three numbers after the change. First, the p99 of the endpoint. Second, the rows and time from the slow log.

Third, the insert time on the same table. If reads improve and writes get worse, you may have added too much index width.

If the read rate is the real limit after the plan is good, add replica capacity. If the write rate is the limit, an extra index can make that worse. Pick the side that is actually hot. Do not buy a bigger primary to hide a scan you could remove.

Key Takeaways

• Read the real plan on production-sized data before you add an index.
• Match index column order to equality filters, then the range or sort.
• Build large indexes concurrently and drop any invalid leftover.
• Fix N+1 loops before you tune a single statement inside the loop.
• Prefer keyset pages over deep offsets so the index can stop early.
• Watch write latency after you add an index, not only read latency.
• Refresh stats after a bulk load so the planner does not price a lie.

FAQ

Should you add an index for every slow query?

No. Add an index when the statement is frequent or sits on a user path, and when the plan shows a scan or a sort you can remove. A rare report can use a scan in a quiet window. Also check whether a tighter filter or a smaller page removes the need.

Why does the index exist but the plan ignore it?

The planner skips an index when it thinks a scan is cheaper. That happens if the predicate matches a large share of the table, if stats are stale, or if a function wraps the column. Print the plan and look for a cast or a filter that does not match the index order.

Is EXPLAIN enough without ANALYZE?

EXPLAIN shows the estimated plan and does not run the statement. Use it when you must not touch live data. EXPLAIN ANALYZE runs the query and shows real time and row counts. When you can, run it on a replica or a restored copy so you do not add load to the primary.

When should you stop tuning and change the product?

Stop when the honest plan still reads too much data for the latency you need. A search across years of events may need a summary table or a narrower screen. More indexes will not make a full history page cheap. Change the question the product asks.

Take the slowest endpoint this week and print its plan on real row counts. Change one index or one statement, then watch p99 and write time for a day before you touch the next query. If the plan is already tight, look at N+1 loops and pool waits next.

Last updated on 15 September 2026.