Chapter 3 · Performance and Production
EXPLAIN and Query Optimization
- Page 9 of 22
- 22 min read
A slow query is rarely fixed by guessing. The database will tell you exactly how it runs a query — which tables it scans, which indexes it uses, in what order it joins, where it sorts — if you ask with EXPLAIN. This page teaches you to read those plans in SQLite, PostgreSQL and MySQL, and then works through the fixes that solve most real slowdowns: filters that can use an index, better joins and subqueries, pagination that does not get slower page by page, and the N+1 pattern that quietly turns one query into thousands.
For AI and data work this matters every day: feature queries run once per training row or per API request, evaluation dashboards page through millions of logged LLM calls, and a pipeline that takes two hours instead of two minutes is a pipeline nobody re-runs.
What you will learn
- Read
EXPLAIN QUERY PLAN(SQLite),EXPLAIN/EXPLAIN ANALYZE(PostgreSQL and MySQL 8) - Write sargable filters, and spot the ones that cannot use an index
- Speed up joins and subqueries, and replace
OFFSETpagination with keyset pagination - Find and fix N+1 queries in Python code
- Use statistics (
ANALYZE), slow query logs and honest benchmarks
Setup: the events table again
This page uses the same 200,000-row events table as Indexes: How They Work and When They Help, and the same helpers, with one improvement: plan() now indents nested steps the way the sqlite3 shell does.
CREATE TABLE events (
event_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
event_type TEXT NOT NULL,
product_id INTEGER NOT NULL,
event_day TEXT NOT NULL,
amount INTEGER NOT NULL
);
INSERT INTO events (event_id, customer_id, event_type, product_id, event_day, amount)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 200000)
SELECT i,
(i * 7919) % 5000 + 1,
CASE WHEN i % 20 = 0 THEN 'purchase' WHEN i % 5 = 0 THEN 'cart' ELSE 'view' END,
(i * 31) % 11 + 1,
date('2026-01-01', '+' || (i % 181) || ' days'),
(i * 37) % 5000 + 100
FROM n;import sqlite3
from sqlhelp import con, show
def plan(sql, params=()):
"""Print SQLite's plan as an indented tree."""
fresh = sqlite3.connect("shop.db") # a new connection always sees the latest indexes
rows = fresh.execute("EXPLAIN QUERY PLAN " + sql, params).fetchall()
fresh.close()
depth = {0: -1}
for step_id, parent, _, detail in rows:
depth[step_id] = depth[parent] + 1
print(" " * depth[step_id] + detail)
def count_steps(sql, params=()):
"""Run a statement; return (rows returned, bytecode steps SQLite executed)."""
steps = 0
def tick():
nonlocal steps
steps += 1
return 0
con.set_progress_handler(tick, 1)
rows = con.execute(sql, params).fetchall()
con.set_progress_handler(None, 1)
return len(rows), steps
def work(sql, params=()):
rows, steps = count_steps(sql, params)
print(f"{rows} rows, {steps:,} steps")Reading a SQLite plan
Start with a familiar shop query — delivered orders per customer:
plan("""
SELECT c.name, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id
ORDER BY orders DESC
""")SCAN o
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BYRead it top to bottom as the order of work. SQLite joins with nested loops: the first line is the outer loop (scan every order), the second is what it does for each order (look up the customer by primary key). Then it groups and sorts, each with a temporary B-tree. No line says "filter on status": a WHERE that cannot use an index is simply checked inside the scan. Lines you will meet:
| Plan line | Meaning |
|---|---|
SCAN t | Read every row of t |
SEARCH t USING INDEX i (col=?) | Jump into index i; the brackets show which conditions the index serves |
USING COVERING INDEX | Everything needed is in the index; the table is not read |
USING INTEGER PRIMARY KEY (rowid=?) | Direct lookup by the table's own key |
USE TEMP B-TREE FOR ORDER BY / GROUP BY / DISTINCT | An extra sort; an index in the right order can remove it |
CORRELATED SCALAR SUBQUERY | A subquery re-run for every outer row — check what it does inside |
AUTOMATIC … INDEX, BLOOM FILTER | SQLite built a temporary index for this one query: a hint that a real index is missing |
MATERIALIZE, CO-ROUTINE | A subquery or CTE computed into a temporary table, or streamed row by row |
EXPLAIN and EXPLAIN ANALYZE in PostgreSQL
PostgreSQL shows more: the estimated cost and number of rows for each step. Build the same table there with generate_series, and gather statistics with ANALYZE (more on that below). Parallel workers are switched off for this session only to keep the plans short:
-- PostgreSQL
CREATE TABLE events AS
SELECT i AS event_id,
(i * 7919) % 5000 + 1 AS customer_id,
CASE WHEN i % 20 = 0 THEN 'purchase' WHEN i % 5 = 0 THEN 'cart' ELSE 'view' END AS event_type,
(i * 31) % 11 + 1 AS product_id,
DATE '2026-01-01' + (i % 181) AS event_day,
(i * 37) % 5000 + 100 AS amount
FROM generate_series(1, 200000) AS i;
ALTER TABLE events ADD PRIMARY KEY (event_id);
ANALYZE events;
SET max_parallel_workers_per_gather = 0;
EXPLAIN SELECT * FROM events WHERE customer_id = 42;+-----------------------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------------------+
| Seq Scan on events (cost=0.00..3972.00 rows=40 width=25) |
| Filter: (customer_id = 42) |
+-----------------------------------------------------------+cost=0.00..3972.00 is start-up cost..total cost, in abstract units (roughly "reading one page in order = 1"). rows=40 is the planner's estimate; width is the average row size in bytes. EXPLAIN alone does not run the query. EXPLAIN ANALYZE does, and adds what really happened. The options below hide the costs and timings so you can focus on the rows:
-- PostgreSQL
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE customer_id = 42;
CREATE INDEX idx_events_customer ON events (customer_id);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE customer_id = 42;+---------------------------------------------+
| QUERY PLAN |
+---------------------------------------------+
| Seq Scan on events (actual rows=40 loops=1) |
| Filter: (customer_id = 42) |
| Rows Removed by Filter: 199960 |
+---------------------------------------------+
+-------------------------------------------------------------------------+
| QUERY PLAN |
+-------------------------------------------------------------------------+
| Bitmap Heap Scan on events (actual rows=40 loops=1) |
| Recheck Cond: (customer_id = 42) |
| Heap Blocks: exact=40 |
| -> Bitmap Index Scan on idx_events_customer (actual rows=40 loops=1) |
| Index Cond: (customer_id = 42) |
+-------------------------------------------------------------------------+Rows Removed by Filter: 199960 is the smell of a missing index: 200,000 rows read to keep 40. After the index, PostgreSQL uses a bitmap scan: the inner step collects the matching row locations from the index, the outer step reads those table pages in physical order (Heap Blocks: exact=40 — 40 pages). For one or two rows it would choose a plain Index Scan; for a big share of the table, a Seq Scan.
Plain EXPLAIN ANALYZE (no options) prints both at once, plus timings: (cost=4.60..145.32 rows=40 width=25) (actual time=0.005..0.022 rows=40 loops=1), then Planning Time and Execution Time at the bottom. Compare the estimated rows with the actual rows on each line. When those differ by 10× or more, the planner is working from bad information, and that — not a missing index — is often the real problem. Watch out: EXPLAIN ANALYZE really executes the statement, so wrap an UPDATE or DELETE in BEGIN; … ROLLBACK;.
EXPLAIN in MySQL 8
MySQL's classic EXPLAIN prints one row per table, with twelve columns. Build the table (MySQL limits recursive CTEs to 1,000 levels unless you raise cte_max_recursion_depth):
-- MySQL
SET SESSION cte_max_recursion_depth = 200000;
CREATE TABLE events (
event_id INT PRIMARY KEY,
customer_id INT NOT NULL,
event_type VARCHAR(10) NOT NULL,
product_id INT NOT NULL,
event_day DATE NOT NULL,
amount INT NOT NULL
);
INSERT INTO events
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 200000)
SELECT i,
(i * 7919) % 5000 + 1,
CASE WHEN i % 20 = 0 THEN 'purchase' WHEN i % 5 = 0 THEN 'cart' ELSE 'view' END,
(i * 31) % 11 + 1,
DATE '2026-01-01' + INTERVAL (i % 181) DAY,
(i * 37) % 5000 + 100
FROM n;The columns that matter most are type, key, rows and Extra, so read the plan from Python and print just those. The connection settings come from environment variables — never write a password into code (see Security: Roles, SQL Injection, Sensitive Data and Backups):
import os
import pymysql
mysql = pymysql.connect(
host=os.environ["MYSQL_HOST"], port=int(os.environ["MYSQL_PORT"]),
user=os.environ["MYSQL_USER"], password=os.environ["MYSQL_PASSWORD"],
database=os.environ["MYSQL_DATABASE"], autocommit=True,
)
cur = mysql.cursor(pymysql.cursors.DictCursor)
cur.execute("ANALYZE TABLE events")
def mysql_plan(sql):
cur.execute("EXPLAIN " + sql)
for row in cur.fetchall():
info = {k: row[k] for k in ("table", "type", "key", "rows", "Extra")}
info["rows"] = round(info["rows"], 1 - len(str(info["rows"]))) # an estimate from sampled pages: keep one significant digit
print(info)
mysql_plan("SELECT * FROM events WHERE customer_id = 42")
cur.execute("CREATE INDEX idx_events_customer ON events (customer_id)")
mysql_plan("SELECT * FROM events WHERE customer_id = 42"){'table': 'events', 'type': 'ALL', 'key': None, 'rows': 200000, 'Extra': 'Using where'}
{'table': 'events', 'type': 'ref', 'key': 'idx_events_customer', 'rows': 40, 'Extra': None}type is the access method, from worst to best: ALL (full scan), index (full index scan), range, ref (index lookup on a non-unique value), eq_ref (one row per join row via a unique key), const (at most one row). Using where in Extra means rows are filtered after reading. rows is an estimate that InnoDB makes from a sample of pages, so it changes a little from run to run (192,827 one time, 199,865 the next) — that is why mysql_plan() rounds it. MySQL 8.0.18+ also has EXPLAIN ANALYZE, which runs the query and prints a tree with estimated and actual rows and times, like PostgreSQL's (EXPLAIN FORMAT=TREE, from 8.0.16, prints the same tree with estimates only, without running the query).
Sargable filters: let the index do its job
A filter is sargable (from "Search ARGument ABLE") when the database can turn it into an index range. The rule: leave the indexed column bare on one side of the comparison. Wrapping it in a function or arithmetic hides it from the index:
CREATE INDEX idx_events_day ON events (event_day);pairs = [
("SELECT COUNT(*) FROM events WHERE substr(event_day, 1, 7) = '2026-03'",
"SELECT COUNT(*) FROM events WHERE event_day >= '2026-03-01' AND event_day < '2026-04-01'"),
("SELECT COUNT(*) FROM events WHERE date(event_day, '+7 days') > '2026-07-01'",
"SELECT COUNT(*) FROM events WHERE event_day > date('2026-07-01', '-7 days')"),
]
for slow, fast in pairs:
for sql in (slow, fast):
plan(sql)
work(sql)
print()SCAN events USING COVERING INDEX idx_events_day
1 rows, 834,361 steps
SEARCH events USING COVERING INDEX idx_events_day (event_day>? AND event_day<?)
1 rows, 102,784 steps
SCAN events USING COVERING INDEX idx_events_day
1 rows, 806,640 steps
SEARCH events USING COVERING INDEX idx_events_day (event_day>?)
1 rows, 13,271 stepsEach pair returns the same count; only the second form can search the index. The same rule covers other traps:
- A leading wildcard.
LIKE 'user42%'can use a B-tree, because a prefix is a range, but only when the index's comparison matchesLIKE's: in SQLiteLIKEis case-insensitive, so it uses the index only on aCOLLATE NOCASEcolumn (or withPRAGMA case_sensitive_like = ON); in PostgreSQL the column needstext_pattern_opsor the C collation (see above).LIKE '%42'can never use it. - Case-folding.
lower(email) = …cannot use an index onemail; index the expression instead. - Type mismatches. In MySQL, comparing a
VARCHARcolumn to a number (WHERE phone = 01711…) converts every row and cannot use the index. Pass the value with the column's type.
Here are the first two on a PostgreSQL table of 100,000 emails:
-- PostgreSQL
CREATE TABLE app_users AS
SELECT i AS user_id, 'user' || i || '@example.com' AS email
FROM generate_series(1, 100000) AS i;
CREATE INDEX idx_app_users_email ON app_users (email text_pattern_ops);
CREATE INDEX idx_app_users_email_lower ON app_users (lower(email));
ANALYZE app_users;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM app_users WHERE email LIKE 'user4242@%';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM app_users WHERE email LIKE '%4242@example.com';
EXPLAIN (COSTS OFF)
SELECT * FROM app_users WHERE lower(email) = 'user4242@example.com';+----------------------------------------------------------------------------------+
| QUERY PLAN |
+----------------------------------------------------------------------------------+
| Index Scan using idx_app_users_email on app_users (actual rows=1 loops=1) |
| Index Cond: ((email ~>=~ 'user4242@'::text) AND (email ~<~ 'user4242A'::text)) |
| Filter: (email ~~ 'user4242@%'::text) |
+----------------------------------------------------------------------------------+
+------------------------------------------------+
| QUERY PLAN |
+------------------------------------------------+
| Seq Scan on app_users (actual rows=10 loops=1) |
| Filter: (email ~~ '%4242@example.com'::text) |
| Rows Removed by Filter: 99990 |
+------------------------------------------------+
+-------------------------------------------------------------+
| QUERY PLAN |
+-------------------------------------------------------------+
| Index Scan using idx_app_users_email_lower on app_users |
| Index Cond: (lower(email) = 'user4242@example.com'::text) |
+-------------------------------------------------------------+The prefix LIKE became an index range (~>=~ … ~<~); the leading % read all 100,000 rows; the expression index served lower(email). Note text_pattern_ops: with a language-aware collation such as en_US.utf8 (the usual default), a plain B-tree index on text cannot serve LIKE at all. For "contains" searches use a trigram index (pg_trgm) or full-text search (see SQL for AI Apps: Embeddings, pgvector and RAG Metadata).
Select only what you need
SELECT * is fine for exploring and costly in code. It sends every column over the network (imagine a 6 KB embedding or prompt column you did not need), it turns a covering-index search into one that must also visit the table, and its result silently changes when someone adds a column. Name the columns you use.
Joins and subqueries
A correlated subquery runs once per outer row. That is fine when the inner query is an index lookup and terrible when it is a scan. "How many purchases has each shop customer made?":
correlated = """
SELECT c.customer_id,
(SELECT COUNT(*) FROM events e
WHERE e.customer_id = c.customer_id AND e.event_type = 'purchase') AS purchases
FROM customers c ORDER BY c.customer_id"""
grouped_first = """
SELECT c.customer_id, COALESCE(p.purchases, 0) AS purchases
FROM customers c
LEFT JOIN (SELECT customer_id, COUNT(*) AS purchases
FROM events WHERE event_type = 'purchase'
GROUP BY customer_id) p ON p.customer_id = c.customer_id
ORDER BY c.customer_id"""
for sql in (correlated, grouped_first):
plan(sql)
work(sql)
print()SCAN c
CORRELATED SCALAR SUBQUERY 1
SCAN e
8 rows, 6,400,802 steps
MATERIALIZE p
SCAN events
USE TEMP B-TREE FOR GROUP BY
SCAN c
BLOOM FILTER ON p (customer_id=?)
SEARCH p USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
8 rows, 715,919 stepsThe correlated version scans 200,000 events once for each of the 8 customers. Aggregating first and joining the small result reads the events once. Notice the AUTOMATIC COVERING INDEX: SQLite built a throwaway index for the join, which is SQLite telling you a real one is missing. Index the join column and even the correlated form becomes cheap:
CREATE INDEX idx_events_customer ON events (customer_id, event_type);plan(correlated)
work(correlated)SCAN c
CORRELATED SCALAR SUBQUERY 1
SEARCH e USING COVERING INDEX idx_events_customer (customer_id=? AND event_type=?)
8 rows, 360 stepsThe lessons generalise. Index the columns you join on (foreign keys first). Aggregate or filter before joining when you can, so the join handles fewer rows. Prefer EXISTS to COUNT(*) > 0 when you only need "is there one?" — it can stop at the first match. PostgreSQL and MySQL rewrite many subqueries into joins on their own (SQLite rewrites fewer), so check the plan instead of assuming either way.
Pagination: OFFSET vs keyset
An evaluation dashboard that shows 10 logged calls per page with LIMIT 10 OFFSET 150000 makes the database produce and throw away 150,000 rows to show page 15,001. Keyset pagination remembers the last key the user saw and continues from there:
offset_page = "SELECT * FROM events ORDER BY event_id LIMIT 10 OFFSET 150000"
keyset_page = "SELECT * FROM events WHERE event_id > ? ORDER BY event_id LIMIT 10"
plan(offset_page)
work(offset_page)
plan(keyset_page, (150000,))
work(keyset_page, (150000,))SCAN events
10 rows, 300,110 steps
SEARCH events USING INTEGER PRIMARY KEY (rowid>?)
10 rows, 101 stepsSame 10 rows, 3,000 times less work, and the cost no longer grows with the page number. The price: you can only go "next" and "previous", not jump to page 15,001, and the sort key must be unique (add the primary key as a tie-breaker: WHERE (created_at, id) > (?, ?) ORDER BY created_at, id).
The N+1 problem from Python
The most common slow pattern in application code is not a slow query but too many fast ones: fetch a list (1 query), then fetch something for each item (N queries). set_trace_callback shows every statement a connection runs:
queries = []
con.set_trace_callback(queries.append)
# N+1: one query for the orders, then one per order for its customer
orders = con.execute("SELECT order_id, customer_id FROM orders ORDER BY order_id").fetchall()
names = {}
for order_id, customer_id in orders:
names[order_id] = con.execute(
"SELECT name FROM customers WHERE customer_id = ?", (customer_id,)).fetchone()[0]
print("N+1 version:", len(queries), "queries")
queries.clear()
rows = con.execute("""
SELECT o.order_id, c.name
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id""").fetchall()
print("join version:", len(queries), "query")
print(dict(rows) == names)
con.set_trace_callback(None)N+1 version: 15 queries
join version: 1 query
TrueWith SQLite in-process, 15 queries cost little. Against a server, each query is a network round trip of perhaps 1 ms, so 10,000 orders means 10 seconds of waiting, against milliseconds for the join. ORMs make N+1 easy to write by accident (looping over order.customer); they also have the fix (eager loading, such as SQLAlchemy's selectinload). Other forms: a WHERE id IN (…) batch, or executemany for writes.
Statistics and ANALYZE
A planner chooses between plans by estimating how many rows each step returns. Those estimates come from statistics gathered by ANALYZE. SQLite has none until you run it:
sql = "SELECT COUNT(*) FROM events WHERE event_type = 'view' AND event_day = '2026-03-15'"
con.execute("CREATE INDEX idx_events_type ON events (event_type)")
plan(sql)
work(sql)
con.execute("ANALYZE")
show("SELECT tbl, idx, stat FROM sqlite_stat1 WHERE tbl = 'events' ORDER BY idx")
plan(sql)
work(sql)SEARCH events USING INDEX idx_events_type (event_type=?)
1 rows, 800,900 steps
+--------+---------------------+--------------+
| tbl | idx | stat |
+--------+---------------------+--------------+
| events | idx_events_customer | 200000 40 40 |
| events | idx_events_day | 200000 1105 |
| events | idx_events_type | 200000 66667 |
+--------+---------------------+--------------+
SEARCH events USING INDEX idx_events_day (event_day=?)
1 rows, 6,427 stepsTwo indexes could serve this query. Without statistics SQLite guessed, picked the index on event_type, and walked 160,000 "view" entries. ANALYZE filled sqlite_stat1: 200000 66667 means 200,000 index entries and about 66,667 rows per event_type value; 200000 1105 means about 1,105 rows per day. Now the planner knows the day is far more selective and switches index — over 100 times less work, from the same query and the same indexes. PostgreSQL's statistics live in pg_stats (distinct values, most common values and their frequencies, histograms):
-- PostgreSQL
SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'events' AND attname IN ('event_type', 'product_id', 'event_day')
ORDER BY attname;
CREATE INDEX idx_events_type ON events (event_type);
EXPLAIN (COSTS OFF) SELECT SUM(amount) FROM events WHERE event_type = 'purchase';
EXPLAIN (COSTS OFF) SELECT SUM(amount) FROM events WHERE event_type = 'view';+------------+------------+
| attname | n_distinct |
+------------+------------+
| event_day | 181.0 |
| event_type | 3.0 |
| product_id | 11.0 |
+------------+------------+
+-----------------------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------------------+
| Aggregate |
| -> Bitmap Heap Scan on events |
| Recheck Cond: (event_type = 'purchase'::text) |
| -> Bitmap Index Scan on idx_events_type |
| Index Cond: (event_type = 'purchase'::text) |
+-----------------------------------------------------------+
+---------------------------------------------+
| QUERY PLAN |
+---------------------------------------------+
| Aggregate |
| -> Seq Scan on events |
| Filter: (event_type = 'view'::text) |
+---------------------------------------------+Same index, two plans: the statistics say purchase is 5% of the rows (use the index) and view is 80% (a sequential scan is cheaper). PostgreSQL's autovacuum and MySQL's InnoDB refresh statistics automatically after enough changes, but after a big bulk load run ANALYZE t (MySQL: ANALYZE TABLE t) yourself before trusting any plan. SQLite never does it on its own; run ANALYZE or PRAGMA optimize now and then.
Finding slow queries in production
You cannot EXPLAIN a query you do not know is slow. Every server can log them:
-- MySQL
SELECT @@slow_query_log, @@long_query_time;+------------------+-------------------+
| @@slow_query_log | @@long_query_time |
+------------------+-------------------+
| 0 | 10.0 |
+------------------+-------------------+-- PostgreSQL
SHOW log_min_duration_statement;+----------------------------+
| log_min_duration_statement |
+----------------------------+
| -1 |
+----------------------------+Both are off by default (MySQL's threshold is 10 seconds when switched on; PostgreSQL's -1 means "never"). An administrator switches them on in the server configuration, or at runtime (MySQL: SET GLOBAL slow_query_log = ON; PostgreSQL: ALTER SYSTEM SET log_min_duration_statement = 500 then SELECT pg_reload_conf()):
# MySQL: my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.5 # seconds
log_queries_not_using_indexes = 1
# PostgreSQL: postgresql.conf
log_min_duration_statement = 500 # milliseconds
shared_preload_libraries = 'pg_stat_statements'Better than a log is an aggregate. With the pg_stat_statements extension loaded at server start (the shared_preload_libraries line above, then a restart) and enabled in your database with CREATE EXTENSION pg_stat_statements, PostgreSQL keeps totals per normalised query, so you find the query that costs most in total — often a fast one called a million times:
-- PostgreSQL (needs pg_stat_statements loaded at server start)
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 2) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;MySQL's equivalent is performance_schema.events_statements_summary_by_digest (or the sys.statement_analysis view).
Benchmarking honestly
When you do time queries, time them fairly. The first run pays to load pages from disk into the cache; later runs do not. One run tells you little. Repeat, take the median, and say what you measured:
import statistics
import time
def benchmark(sql, params=(), repeat=20):
con.execute(sql, params).fetchall() # warm-up run, not counted
times = []
for _ in range(repeat):
start = time.perf_counter()
con.execute(sql, params).fetchall()
times.append((time.perf_counter() - start) * 1000)
return statistics.median(times)
print(f"OFFSET page: {benchmark(offset_page):.3f} ms (median of 20)")
print(f"keyset page: {benchmark(keyset_page, (150000,)):.3f} ms (median of 20)")OFFSET page: 1.513 ms (median of 20)
keyset page: 0.011 ms (median of 20)- Benchmark with production-sized data: on 14 rows every plan is fast, and planners choose differently on small tables.
- Change one thing at a time, and keep the plan next to the timing — the plan explains the number.
- Measure the whole path the user waits for (network, driver, fetching rows), not only the database.
- Do not trust a single fast run of a query whose plan still says
SCANon a big table: it was probably cached.
An optimisation routine
- Find the slow or expensive query: slow log,
pg_stat_statements, or by counting queries per request (N+1). - Explain it:
EXPLAINfirst, thenEXPLAIN ANALYZEon a copy or inside a rolled-back transaction. - Look for full scans that remove most rows, estimates far from actual rows, sorts and temporary B-trees, subqueries run per row.
- Fix the smallest thing: make the filter sargable, add or reorder an index, select fewer columns, aggregate before joining, use keyset pagination, refresh statistics.
- Verify: same results, better plan, then the timing.
Common mistakes
- Optimising without a plan. Indexes added at random slow down every write and often fix nothing. Read the plan first and fix what it shows.
- Running
EXPLAIN ANALYZEon a write. It executes the statement. UseBEGIN; EXPLAIN ANALYZE DELETE …; ROLLBACK;. - Functions on indexed columns.
WHERE strftime('%Y', event_day) = '2026'scans;WHERE event_day >= '2026-01-01' AND event_day < '2027-01-01'searches. If you really need the expression, index the expression. - Queries in a loop. Fetch related rows with one join or one
IN (…)query; count queries per request in tests.
Try it yourself
- Easy:
SELECT COUNT(*) FROM events WHERE amount * 2 > 9000scans. Add an index onamount, rewrite the filter so it is sargable, and show both plans. - Medium: Write keyset pagination over
eventsordered byevent_dayand thenevent_id, returning the 5 rows after ('2026-03-01',100). Which index makes it a single index search? - Hard: Rewrite this N+1 code as a single query and prove with
set_trace_callbackthat it runs once: for every product, fetch the total quantity sold fromorder_itemswith one query per product.
Answers
# 1. Easy: move the arithmetic to the constant side
con.execute("CREATE INDEX idx_events_amount ON events (amount)")
plan("SELECT COUNT(*) FROM events WHERE amount * 2 > 9000")
plan("SELECT COUNT(*) FROM events WHERE amount > 4500")# 2. Medium: a row-value comparison on (event_day, event_id).
# Every SQLite index ends with the rowid, so idx_events_day already is (event_day, event_id);
# in PostgreSQL or MySQL create it: CREATE INDEX idx_events_day_id ON events (event_day, event_id)
page = """SELECT event_id, event_day, event_type FROM events
WHERE (event_day, event_id) > (?, ?)
ORDER BY event_day, event_id LIMIT 5"""
plan(page, ("2026-03-01", 100))
show(page, ("2026-03-01", 100))# 3. Hard
queries = []
con.set_trace_callback(queries.append)
rows = con.execute("""
SELECT p.product_id, p.name, COALESCE(SUM(oi.quantity), 0) AS sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY p.product_id""").fetchall()
con.set_trace_callback(None)
print(len(queries), "query,", len(rows), "products")Summary
EXPLAINshows the plan;EXPLAIN ANALYZE(PostgreSQL, MySQL 8.0.18+) runs the query and adds actual rows and times. Compare estimated and actual rows.- Keep indexed columns bare in filters (sargable): no functions, arithmetic, leading
%or type conversions on the column side. - Index join columns, aggregate before joining, and watch for subqueries that run per row.
- Keyset pagination costs the same on every page;
OFFSETgets slower and slower. N+1 query loops become one join. - Planners are only as good as their statistics:
ANALYZEafter bulk loads. Find slow queries with slow logs andpg_stat_statements; benchmark with warm-ups, repeats and medians.
Next: Views, Stored Procedures, Functions and Triggers moves logic into the database itself: saved queries, materialized views that cache an expensive result, procedures and functions, triggers that keep an audit trail, and when that logic belongs in the database rather than in your Python code.