Indexes & performance.
From 10 seconds to 10 milliseconds.
A query that runs instantly on 1,000 rows takes 10 seconds on 1 million rows. Same query, different scale. The difference between "works in development" and "works in production" is almost always indexes. This lesson teaches what they are, when to add them, when not to, how to read EXPLAIN plans, and the specific patterns that turn slow queries fast. It's the last lesson in the SQL track — and the one that separates hobbyist SQL from production SQL.
An index is a B-tree that lets the database jump directly to matching rows instead of scanning every row. It turns WHERE user_id = 42 from O(n) to O(log n). Add indexes on columns used in WHERE, JOIN ON, and ORDER BY. Don't index everything — each index slows writes and uses storage. Use EXPLAIN to see if a query uses indexes. If it says "Seq Scan" or "Full Table Scan", that's your signal to add one. If it says "Index Scan" or "Index Only Scan", you're good.
Before this lesson: complete Lesson 5. You should be comfortable with SELECT, WHERE, JOIN, GROUP BY.
📚 What an index actually is
A data structure, not magic. Like the index at the back of a book — but for database rows.
Without an index, finding a row requires scanning the entire table. On a table with 1,000 rows, that's instant. On a table with 1 million rows, that's seconds. An index is a separate data structure that maps column values to row locations, so the database can jump straight to the matches.
-- Query without an index on user_id SELECT * FROM orders WHERE user_id = 42; -- The database does this: -- 1. Read row 1 → check user_id → no -- 2. Read row 2 → check user_id → no -- 3. Read row 3 → check user_id → no -- ... (repeats for all rows) -- 1,000,000. Read row 1,000,000 → check → no -- Result: 1,000,000 row reads. -- Time on a real table: 500-2000ms. -- This is called a "full table scan". -- The cost grows linearly with table size: -- 1K rows = 1ms, 100K = 100ms, 1M = 1000ms. -- If you see this pattern on a large table, -- add an index. It's the single biggest -- performance win in SQL.
The B-tree index structure (simplified)
[ 500 ] / [ 250 ] [ 750 ] / \ / [100] [350] [600] [900] / \ / \ / \ / 1..99 100..249 250..499 ... 900..999 Each leaf points to actual row locations in the table. To find user_id = 42: 1. Compare 42 to 500 → go left 2. Compare 42 to 250 → go left 3. Compare 42 to 100 → go left 4. Scan the 1..99 leaf → find 42 Only 4 comparisons for 1M rows. That's the entire magic of a B-tree.
B-tree structure
The default index type. Balanced tree where each node has multiple children. Lookup is O(log n) — 20 steps to find any row in 1 million. Handles range queries (WHERE x > 100) efficiently.
Separate from the table
An index is a separate file/area. It stores (column_value → row_location) pairs. That's why indexes use extra storage — typically 10-20% of table size.
Automatically maintained
When you INSERT, UPDATE, or DELETE, all indexes on the table are updated. You don't manage them manually. This is why writes are slower on indexed tables.
Primary key = index
Every PRIMARY KEY is automatically indexed. Every UNIQUE constraint creates an index. That's why WHERE id = 42 is always fast — id is the primary key.
Think of a B-tree like a phone book. To find "Smith, John," you don't read every page — you go to the S section, then Smith, then John. Without the alphabetical order, you'd have to scan every name. An index adds alphabetical order to a column so the database can do the same trick. It's not magic; it's just sorted data with extra metadata to make lookups fast. That's the entire concept.
🔍 EXPLAIN — the diagnostic tool
Tells you how the database will run the query. The first tool to check when something's slow.
-- Show the query plan WITHOUT running it EXPLAIN SELECT * FROM orders WHERE user_id = 42; -- Show the query plan WITH actual runtime (slower, runs query) EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; -- Read bottom-up: innermost operation first. -- Look for: -- • "Seq Scan" or "Full Table Scan" ← bad -- • "Index Scan" or "Index Only Scan" ← good -- • Rows estimated vs actually returned -- • Total cost / actual time -- • Rows scanned vs rows returned
❌ Bad — no index
Seq Scan on orders (cost=0.00..18500.00 rows=1 width=48) (actual time=0.015..842.315 rows=1 loops=1) Filter: (user_id = 42) Rows Removed by Filter: 999999
Took 842ms because it scanned 1,000,000 rows. "Rows Removed by Filter: 999999" is the giveaway.
✅ Good — with index
Index Scan using idx_orders_user_id on orders (cost=0.42..8.44 rows=1 width=48) (actual time=0.015..0.017 rows=1 loops=1) Index Cond: (user_id = 42)
Seq Scan
PostgreSQL's term for full table scan. Reads every row. Fine for small tables, catastrophic for large ones. If you see this on a WHERE filter that returns few rows, add an index.
Index Scan
The good outcome. The database uses an index to find matching rows, then looks up the full rows. Fast. Your goal for any frequent query.
Index Only Scan
Best case. The database answers the query from the index alone, without reading the table. Requires a covering index (see below). Fastest possible.
Rows Removed by Filter
The killer metric. If this number is high (like 999,999), the query is doing massive wasted work. Every one of those rows was read and discarded. That's the number EXPLAIN is telling you to fix.
Always run EXPLAIN before deploying any query that might be slow. Especially on tables that will grow. A query that takes 5ms on 1,000 rows might take 5 seconds on 1 million. EXPLAIN ANALYZE reveals this before your users do. If the plan shows a Seq Scan on a table over 10,000 rows with a selective filter, add an index. This is 5 minutes of work that prevents a production incident.
EXPLAIN ANALYZE actually runs the query — use it carefully. EXPLAIN alone shows the plan without executing. EXPLAIN ANALYZE runs the query and reports real timing. That's more useful — but on a slow query, it can still take 10 seconds. On production, use plain EXPLAIN first to see if the plan looks wrong, then EXPLAIN ANALYZE on a replica or with a row limit to get the actual timing.
✅ When to index — and when not to
Every index has a cost. The craft is knowing which ones to add.
-- ✅ ADD an index — foreign keys (always) CREATE INDEX idx_orders_user_id ON orders (user_id); CREATE INDEX idx_order_items_order_id ON order_items (order_id); CREATE INDEX idx_order_items_product_id ON order_items (product_id); -- ✅ ADD an index — frequently filtered columns CREATE INDEX idx_users_email ON users (email); CREATE INDEX idx_users_country ON users (country); -- ✅ ADD an index — columns used in ORDER BY + LIMIT CREATE INDEX idx_posts_created_at ON posts (created_at DESC); -- ⚠️ CONSIDER before adding — low cardinality -- An index on a boolean active column (50/50 values) -- doesn't help much — a full scan is nearly as fast. -- Index only if active = true returns < 5% of rows. -- ⚠️ CONSIDER before adding — very small tables -- A 100-row table scans in 1ms. An index adds -- overhead for no measurable benefit. -- Add indexes when tables grow past ~10,000 rows. -- ❌ DON'T index — LIKE with leading wildcard -- WHERE name LIKE '%alice%' -- The index can't help — the pattern could match -- anywhere. Use a full-text index or trigram index. -- ❌ DON'T index — columns never queried -- Adding an index "just in case" wastes storage -- and slows every INSERT/UPDATE/DELETE. -- Verify with EXPLAIN after adding: EXPLAIN SELECT * FROM orders WHERE user_id = 42; -- Should now show "Index Scan using idx_orders_user_id"
✅ Always index these
- Every foreign key (user_id, order_id, product_id)
- Columns used in frequent WHERE clauses
- Columns used in ORDER BY (with LIMIT)
- Columns used in JOIN ON clauses
- Unique columns (email, username)
❌ Don't index these
- Columns you never filter or sort by
- Boolean columns (50/50 distribution)
- Tables under ~10,000 rows
- Columns used only with LIKE '%x%'
- Every column "just in case"
Foreign keys — always
Every *_id column that references another table's id needs an index. Without it, every JOIN scans the full table. This is the #1 missing index in most applications.
Selectivity matters
An index works best when the column has many unique values and you filter to few rows. Email (high selectivity) benefits. Boolean (low selectivity) doesn't — the index might be slower than a scan.
The cost of indexes
Storage: 10-20% of table size per index. Writes: every INSERT/UPDATE/DELETE updates all indexes — 5 indexes = 5 extra writes per change. Every index is a trade.
Find unused indexes
PostgreSQL tracks index usage in pg_stat_user_indexes. Indexes with idx_scan = 0 after months of use are probably unnecessary. Drop them — they're pure cost.
Index everything is a beginner mistake. Index nothing is also a beginner mistake. The right answer depends on your query patterns. Start with foreign keys (always needed), then add indexes on columns that appear in your most-frequent queries. Measure with EXPLAIN. Drop indexes that aren't being used. The right number of indexes is "as many as the queries need, and not one more."
🔗 Composite indexes — multiple columns
Index on (a, b) helps queries filtering on a, or a AND b. Not b alone. Order matters.
-- Composite index on (user_id, created_at) CREATE INDEX idx_orders_user_created ON orders (user_id, created_at); -- This index HELPS these queries: -- ✅ WHERE user_id = 42 -- ✅ WHERE user_id = 42 AND created_at > '2026-01-01' -- ✅ WHERE user_id = 42 ORDER BY created_at DESC -- ✅ WHERE user_id = 42 ORDER BY created_at LIMIT 10 -- This index does NOT help: -- ❌ WHERE created_at > '2026-01-01' (created_at is second) -- ❌ ORDER BY created_at DESC (created_at is second) -- ❌ WHERE user_id > 42 (range, but only first col) -- Why: the B-tree sorts by user_id FIRST, then created_at -- WITHIN each user_id. So it can jump to a user_id quickly, -- but can't find "all rows after date X" without scanning -- every user's section. -- To support queries on created_at alone, add a separate index: CREATE INDEX idx_orders_created_at ON orders (created_at DESC); -- The rule: put the most-filtered column FIRST. -- If queries filter by user_id 90% of the time and -- created_at 10% of the time, use (user_id, created_at). -- If the reverse, use (created_at, user_id).
A composite index on (user_id, created_at) is like a phone book sorted by last name, then first name. Finding "Smith" is fast. Finding "John" (any last name) is slow — you have to scan every Smith.
✅ Uses the (user_id, created_at) index
WHERE user_id = 42 AND created_at > '2026-01-01'
❌ Does NOT use the index
WHERE created_at > '2026-01-01'
Leftmost prefix rule
A composite index on (a, b, c) can be used for queries filtering on (a), (a, b), or (a, b, c). Not on (b) alone, not on (c) alone. The leftmost columns are mandatory.
Column order matters
Put the column filtered most often first. Put the column with highest selectivity next. Put the column used in ORDER BY last (to satisfy "ORDER BY b" within "WHERE a").
One composite vs many singles
One index on (user_id, created_at) covers queries on user_id alone (via the first column). A separate (created_at) index is still needed for queries on created_at alone. Consolidate when possible.
Range columns go last
If you filter WHERE a = ? AND b > ?, index (a, b) is optimal. If it's WHERE a > ? AND b = ?, use (b, a) — the equality column first. Range conditions should be the last column.
🛡️ Covering indexes — answer from the index alone
The fastest index. Includes all columns the query needs, so the table is never read.
-- Query we want to speed up SELECT id, name, email FROM users WHERE email = 'alice@example.com'; -- Regular index on (email): -- 1. Index scan → find matching row location -- 2. Table read → fetch id, name, email from the row -- 3. Return 1 row -- Two lookups per row. Fine for 1 row. -- Covering index on (email, id, name): -- 1. Index scan → find matching entry -- 2. Return id, name directly from the index -- Table never read! Fastest possible. CREATE INDEX idx_users_covering ON users (email, id, name); -- Verify it's used as "Index Only Scan": EXPLAIN SELECT id, name, email FROM users WHERE email = 'alice@example.com'; -- Plan: Index Only Scan using idx_users_covering
The difference is one extra step. That step is a full disk seek — the slowest operation on modern hardware.
Regular index (email)
Index Scan using idx_users_email (cost=0.42..8.44 rows=1 width=48) Index Cond: (email = 'alice@example.com') → Then reads table for id, name → ~0.15ms
Covering index (email, id, name)
Index Only Scan using idx_users_covering (cost=0.42..4.44 rows=1 width=48) Index Cond: (email = 'alice@example.com') Heap Fetches: 0 → Never reads the table → ~0.02ms (7x faster)
What it is
An index that includes every column the query SELECTs. The database answers from the index alone — "Index Only Scan" in EXPLAIN. The table file is never opened.
How to build one
CREATE INDEX idx ON users (filtered_column, output_column1, output_column2). Include the WHERE columns first, then the SELECT columns. Query must only reference those columns.
Performance gain
2-10x faster than a regular index for the covered query. The saved step is a disk seek — the slowest operation. On read-heavy systems, this adds up significantly.
The tradeoff
Larger index (includes all output columns). Still updated on every write. And only helps queries whose SELECT matches the indexed columns — changing the query breaks the coverage.
Covering indexes are where 10x speedups come from. Going from "Seq Scan 842ms" to "Index Scan 0.15ms" is a 5,600x speedup from adding one index. Going from "Index Scan 0.15ms" to "Index Only Scan 0.02ms" is another 7x. Both are real production gains — the first is often necessary, the second is a refinement for query-dense tables (dashboards, search, feeds). Use them where they matter, not on every query.
🏆 Challenge
Three tasks. Each exercises a different performance scenario.
Task 1 — Diagnose the slow query
Here are three EXPLAIN outputs. For each, identify the problem and the fix:
Query A: Seq Scan on users WHERE email = 'x' — 1M rows, Rows Removed: 999,999
Query B: Seq Scan on posts WHERE created_at > '2026-01-01' — 500K rows, Rows Removed: 480,000
Query C: Index Scan using idx_user_id, but returns 500,000 rows out of 1M — cost 5000
Query D: Seq Scan on orders, then Seq Scan on users, joined in memory — 100 rows returned
Which need indexes? Which need composite indexes? Which need covering indexes? Which are fine as-is?
Task 2 — Design the index strategy
You have a products table (id, name, category, price, stock, created_at) with 500,000 rows. The following queries run frequently:
1. SELECT name, price FROM products WHERE category = 'Electronics' ORDER BY price ASC LIMIT 20;
2. SELECT * FROM products WHERE stock > 0 AND price < 100;
3. SELECT name FROM products WHERE name LIKE 'iPhone%';
4. SELECT * FROM products WHERE created_at > NOW() - INTERVAL '7 days';
5. SELECT category, COUNT(*), AVG(price) FROM products GROUP BY category;
For each query, decide:
• What index would help?
• Single-column, composite, or covering?
• Where should the index go?
• Is it worth the write cost?
Task 3 — Fix the slow application
You inherit an app with these problems. Propose a fix for each:
1. The dashboard takes 8 seconds to load — it joins 4 tables.
2. The "search users" feature has no index on email, so lookups scan 500K rows.
3. Every page view counts clicks with UPDATE post SET click_count = click_count + 1 WHERE id = ? — 1,000 views/sec.
4. A report runs SELECT * FROM orders WHERE user_id = ? but only needs 3 columns.
5. A user_list table has 8 indexes, and every INSERT takes 400ms.
For each, propose: what index to add, what to drop, what to change in the schema, or what to change in the query. Explain the tradeoff. (Hint: for #3, think about write amplification — every write hits every index.)
⚠️ Common mistakes
The performance bugs that catch every developer.
Every user_id, order_id, product_id column needs an index. Without it, every JOIN scans the full table. This is the single biggest missing index in every application.
Fetches every column, including large text and blob columns. Fix: list the columns you actually need. A covering index can then answer the query from the index alone.
WHERE LOWER(email) = 'x' can't use a plain index on email — LOWER(email) is a different value. Fix: use a functional index (CREATE INDEX ON users (LOWER(email))) or normalize the data.
WHERE name LIKE '%alice%' can't use a B-tree index — the pattern could match anywhere. Fix: use full-text index, trigram index, or accept the scan.
8 indexes on a write-heavy table means every INSERT updates 8 indexes. Writes get slow. Fix: audit with EXPLAIN and pg_stat_user_indexes. Drop unused ones.
After bulk INSERT or DELETE, the query planner has stale statistics. Fix: run ANALYZE table_name to update. Postgres auto-analyzes, but explicit helps after big changes.
CREATE INDEX ON orders (created_at, user_id) doesn't help WHERE user_id = 42 (user_id is second). Fix: put the most-filtered column first.
A query that took 5ms on staging (1,000 rows) takes 5 seconds on production (1 million rows). Fix: run EXPLAIN ANALYZE on a production-sized dataset before shipping.
🎓 SQL track complete
Six lessons. One complete skill. Here's what you can do now.
You can write real queries
SELECT, WHERE, JOINs, GROUP BY, subqueries, window functions — every query pattern that appears in production. You can translate business questions into SQL.
You can modify data safely
INSERT, UPDATE, DELETE with transactions. UPSERT to sync safely. Soft delete for user data. You know what not to do — and how to recover if you do.
You can debug slow queries
EXPLAIN, EXPLAIN ANALYZE, index analysis. You know when a query needs an index, a composite index, or a covering index. And when adding one is a mistake.
You can design schemas
Normalization, foreign keys, indexes, soft delete patterns. You know how to build a schema that supports queries without breaking under load.
What to do before moving to advanced SQL. Take a real database (a sample dataset like the Chinook music store, or Sakila DVD rentals). Write 20 queries against it — reports, dashboards, analytics. Run EXPLAIN on each. Add indexes where the plan is bad. This practice is the entire education — the syntax is small, the practice is infinite. Once you've written 100 real queries against real data, you're no longer a beginner.
➡️ What's next
You've completed the SQL track. Three directions from here.
❓ Frequently asked questions
What is a SQL index?
An index is a data structure that speeds up lookups on a column, similar to a book's index. Without an index, the database scans every row. With one, it jumps directly using a B-tree. The tradeoff: indexes speed up reads but slow down writes.
When should I add an index?
Add an index on any column you regularly filter by (WHERE), join on (ON), or sort by (ORDER BY). The most important: foreign keys. If a query is slow and it's filtering on an unindexed column, adding an index typically makes it 10-1000x faster.
When should I NOT add an index?
Don't index: columns you never filter by, low-cardinality columns (booleans), very small tables, columns used with LIKE '%x%', or columns in tables with very heavy write load. Every index is a tradeoff between read speed and write speed.
What is a full table scan?
A full table scan is when the database reads every row to find matches. Fine for small tables (under 1,000 rows), catastrophic for large ones (1M rows = seconds). EXPLAIN shows 'Seq Scan' (PostgreSQL) or 'ALL' (MySQL) when this is happening.
What is EXPLAIN in SQL?
EXPLAIN shows the query plan — how the database intends to execute your query. It reveals which indexes are being used, which tables are being scanned, and where the time is going. EXPLAIN ANALYZE actually runs the query and shows real timing.
What is a composite index and why does column order matter?
A composite index covers multiple columns. Column order matters because the B-tree sorts by the first column, then the second. A query WHERE a = 42 AND b = 10 can use (a, b). A query WHERE b = 10 alone cannot. Put the most-filtered column first.
What is a covering index?
A covering index includes all columns the query needs, so the database can answer from the index alone without reading the table. Dramatically faster for read-heavy queries. The tradeoff: more storage.
How many indexes should a table have?
No universal number — depends on read/write ratio. Read-heavy tables might have 10; write-heavy might have 2. Rule: every foreign key gets an index. Every column in frequent WHERE gets an index. Then stop. Use EXPLAIN to verify which indexes are used; drop unused ones.