Query Optimization and EXPLAIN
What you'll build
An optimized set of queries for a large orders/customers schema, with before/after EXPLAIN plans showing the impact of indexes and query rewrites.
Goals
- Read and interpret EXPLAIN output
- Identify sequential scans and high-cost nodes
- Create effective indexes for query patterns
- Rewrite queries for better performance
Prerequisites
- JOINs and WHERE clauses
- Basic index knowledge
- Aggregate queries
Steps
- 1
Set up a large dataset
Create orders and customers tables with sample data to demonstrate optimization techniques.
CodeCREATE TABLE customers ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total NUMERIC(10,2) NOT NULL, status TEXT NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); -- Insert sample data (run multiple times for scale) INSERT INTO customers (name, city) SELECT 'Customer ' || i, 'City ' || (i % 10) FROM generate_series(1, 10000) i; INSERT INTO orders (customer_id, total, status) SELECT (i % 10000) + 1, (i % 1000)::numeric, CASE WHEN i % 3 = 0 THEN 'shipped' ELSE 'pending' END FROM generate_series(1, 100000) i;Expected result
10000 customers and 100000 orders inserted.
- 2
Analyze a slow query with EXPLAIN
Run EXPLAIN ANALYZE on a query that filters orders by status and joins to customers. Look for Seq Scan (full table scan) and high cost numbers.
CodeEXPLAIN ANALYZE SELECT c.name, o.total, o.status FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'shipped' ORDER BY o.created_at DESC LIMIT 100;Expected result
The plan shows a Seq Scan on orders (no index) and a sort step — both are slow on large tables.
- 3
Add indexes and re-analyze
Create indexes on the filter and sort columns, then re-run EXPLAIN ANALYZE to see the improvement.
Hint
CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_created ON orders(created_at DESC);
- 4
Optimize an aggregate query
Find the total revenue per city. First write the naive version, then optimize by ensuring the join and group-by columns are indexed.
Hint
EXPLAIN ANALYZE SELECT c.city, SUM(o.total) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.city; — add an index on orders(customer_id).
- 5
Rewrite a correlated subquery
A correlated subquery can be slow. Rewrite 'customers who have orders over 500' from a correlated subquery to a JOIN for better performance.
Code-- Slow: correlated subquery EXPLAIN ANALYZE SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500); -- Fast: JOIN with DISTINCT EXPLAIN ANALYZE SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.total > 500;Expected result
The JOIN version typically produces a better plan with a hash join instead of nested loops.
Try it yourself
-- Analyze this query's performance
EXPLAIN ANALYZE
SELECT c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped';CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_customer ON orders(customer_id);
SELECT c.name, o.total FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'shipped';What you learned
You learned to read EXPLAIN plans, identify bottlenecks (Seq Scans, sorts, nested loops), and optimize with targeted indexes and query rewrites. This is the core skill of database performance tuning.
Related Resources
Related Lessons
- Migrations
Schema evolution with indexes.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.