Lesson 10: Measure before optimizing
Use the query plan to find expensive scans, joins, sorts, and estimates that differ from actual row counts. Indexes speed some reads but consume storage and add write cost.
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total)
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY customer_id;EXPLAIN ANALYZE executes the query. Use it carefully for statements with side effects; PostgreSQL supports wrapping experiments in a transaction and rolling back.
Performance and safety checklist
| Practice | Why it matters | Example |
|---|---|---|
| EXPLAIN | Shows the planner's estimated plan without executing the statement. | EXPLAIN SELECT * FROM orders WHERE customer_id=42; |
| EXPLAIN ANALYZE | Runs the query and reports actual timing and rows. | EXPLAIN (ANALYZE, BUFFERS) SELECT ...; |
| B-tree index | Useful for equality, range, and ordered access patterns. | CREATE INDEX ON orders(customer_id, order_date); |
| Partial index | Indexes rows meeting a stable predicate. | CREATE INDEX ON orders(order_date) WHERE status='open'; |
| Composite index order | Leading columns affect which predicates can use the index. | (customer_id, order_date) supports customer lookups first. |
| Parameterized query | Keeps values separate from SQL syntax and prevents injection. | SELECT * FROM users WHERE email = $1 |
| SELECT needed columns | Reduces network and memory work; avoids fragile SELECT *. | SELECT id, status FROM orders |
| Limit results | Protects exploratory queries and user-facing pages. | ORDER BY created_at DESC LIMIT 100 |
| Check cardinality | Join duplication can inflate aggregates. | Compare row counts before and after each join. |
| Transactions | Keep related changes atomic. | BEGIN; ... COMMIT; |
Capstone: customer order report
Build a report showing each customer, paid-order count, lifetime revenue, most recent order, and rank within their region. Use a CTE to aggregate orders, join customers and regions, and a window function to rank totals. Validate with a small known dataset, then inspect EXPLAIN for the production-sized query.
WITH totals AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue,
MAX(order_date) AS latest_order
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
)
SELECT c.name, t.order_count, t.revenue, t.latest_order,
RANK() OVER (PARTITION BY c.region_id ORDER BY t.revenue DESC) AS region_rank
FROM totals t
JOIN customers c USING (customer_id)
ORDER BY c.region_id, region_rank;SQL dialect note
This course uses PostgreSQL syntax for examples such as ILIKE, RETURNING, identity columns, date_trunc, and JSONB. SQL Server, MySQL, SQLite, and Oracle differ in functions, paging syntax, data types, and DDL details. Check the documentation for your database before running statements.
Progress stays in this browser.