Performance: measure before you optimise
Lesson 1 of 4
1Performance: measure before you optimise
The big pictureDevelopers guess where the slowness is and are usually wrong. Measuring points you to the real line, which is usually a query repeated in a loop or a table without an index, not a complex algorithm.
What the picture shows: The performance loop: every optimisation starts with a measurement and ends with one that proves it helped.
Map of this part
The concepts in order, click any one to jump to it:
Two ways into the same idea
If you are late for work every day, do not buy a faster car before you know where the time goes. Maybe it is 30 minutes looking for your keys. Measuring finds the keys; the faster car saves two minutes.
The average hides the worst: p95 = 95% of requests are faster than this number, and p99 reveals the tail your most active users live in. Tools: cProfile and py-spy in Python, the browser tools and clinic.js in Node. N+1: one query for the list, then one per item, fixed with a JOIN, select_related/prefetch_related or IN (...). Indexes: EXPLAIN ANALYZE reveals a Seq Scan on a big table, and a composite index follows the order of the condition's columns. In code: searching a list, O(n), inside a loop becomes O(n²), and a set makes it O(1). And Amdahl's law: speeding up a part that takes 5% of the time saves at most 5%.
Before You Start
🗄️SQL
JOINs and basic indexes.
🧮Time complexity
You know O(n) and O(n²).
The Core Idea
1Percentiles, not the average
Ten requests, nine fast and one very slow. The average looks acceptable, but p95 shows some users wait two seconds:
import statistics
latencies_ms = [80, 85, 90, 95, 100, 110, 120, 130, 140, 2100]
def percentile(values, p):
ordered = sorted(values)
index = max(0, round(p / 100 * len(ordered)) - 1)
return ordered[index]
print("average:", statistics.mean(latencies_ms), "ms")
print("p50 :", percentile(latencies_ms, 50), "ms")
print("p95 :", percentile(latencies_ms, 95), "ms")2N+1: the silent killer
We count queries instead of time, since the count is the problem. A page with 50 orders runs 51 queries, and the fix is one query with a JOIN:
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
""")
db.executemany("INSERT INTO customers VALUES (?, ?)", [(i, f"c{i}") for i in range(1, 11)])
db.executemany("INSERT INTO orders VALUES (?, ?, ?)", [(i, i % 10 + 1, i * 5) for i in range(1, 51)])
queries = 0
def run(sql, args=()):
global queries
queries += 1
return db.execute(sql, args).fetchall()
for order_id, customer_id, total in run("SELECT * FROM orders"):
run("SELECT name FROM customers WHERE id = ?", (customer_id,))
print("N+1 version :", queries, "queries")
queries = 0
rows = run("SELECT o.id, c.name, o.total FROM orders o JOIN customers c ON c.id = o.customer_id")
print("JOIN version:", queries, "query,", len(rows), "rows")3The index and EXPLAIN
EXPLAIN tells you how the database will run the query. Before the index it scans the whole table; after, it jumps straight there:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT)")
query = "SELECT id FROM orders WHERE customer_id = 7 AND status = 'new'"
def plan():
return db.execute("EXPLAIN QUERY PLAN " + query).fetchall()[0][-1]
print("before:", plan())
db.execute("CREATE INDEX idx_orders_customer_status ON orders (customer_id, status)")
print("after :", plan())EXPLAIN ANALYZE to see the real time, and look for a Seq Scan on big tables.Worked example
Problem: the admin dashboard takes 6 seconds, and the team proposes rewriting it in Go. Measurement: the profiler says 95% of the time is in the database, with 312 queries per load. Cause: N+1 in the order list with the customer and branch names. Fix: one JOIN and an index on (status, created_at). Result: 4 queries and 180ms, without changing the language.
Myth vs Reality
| Myth | Reality |
|---|---|
| A faster language fixes slowness. | Most slowness lives in the network and the database, not language speed. |
| An index on every column is faster. | Every index slows writes and takes space. Index what you actually search by. |
Test your understanding
Why does p95 matter more than the average?
One very slow request disappears into the average.
A page with 100 items runs 101 queries. What is the problem?
One query for the list and one per item.
EXPLAIN shows a Seq Scan on a million-row table. First fix?
An index turns the full scan into a direct jump.
Quick review cards
Check Your Understanding
What is the first thing you do with a slowness report?
Measure: which request, what p95, and where the time goes.
How do you make if x in items faster inside a loop?
Turn items into a set once before the loop.
Why does N+1 not show in development?
Because there is little data, so 6 queries go unnoticed. Count queries, not time.
Resources for this lesson
Grouped by type. Start with the official docs for precision, or an article or video for the explanation.
Official docs2
Done with this lesson?
Sign in to save your progress.