Lesson 11 • Expert
Indexes & Performance
Speed up queries by 100x with the right indexes — and learn to read EXPLAIN plans to prove it.
🎯 What You'll Learn
- Create, use, and drop indexes
- Single-column vs. composite (multi-column) indexes
- Read EXPLAIN plans to diagnose slow queries
- Common mistakes that prevent index usage
- Advanced: covering indexes and partial indexes
What Are Indexes?
An index is a data structure that helps the database find rows faster — like a book's index that tells you which page to turn to instead of reading every page.
📖 Real-World Analogy
Without an index, finding "PostgreSQL" in a 500-page book means reading every page (full table scan). With the book's index, you look up "PostgreSQL → page 247" and jump straight there. Database indexes work the same way.
| Index On | Speeds Up | When |
|---|---|---|
| WHERE columns | Filtering | Column in WHERE clause |
| JOIN columns | Table joining | Foreign keys |
| ORDER BY columns | Sorting | Frequently sorted |
| GROUP BY columns | Grouping | Aggregation queries |
⚠️ The Trade-off
Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE) because the index must be updated too. Don't index every column — only the ones you query frequently.
Creating & Managing Indexes
Create single, unique, and composite indexes
-- Create a basic index
CREATE INDEX idx_customer_email
ON customers(email);
-- Create a unique index
CREATE UNIQUE INDEX idx_username
ON users(username);
-- Multi-column (composite) index
CREATE INDEX idx_orders_cust_date
ON orders(customer_id, order_date);
-- Drop an index
DROP INDEX idx_customer_email;
-- View existing indexes (PostgreSQL)
SELECT indexname, tablename
FROM pg_indexes
WHERE schemaname = 'public';EXPLAIN — Reading Query Plans
See how the database executes your queries
-- EXPLAIN shows how the database runs your query
EXPLAIN SELECT * FROM orders
WHERE customer_id = 100;
-- EXPLAIN ANALYZE actually runs it and shows timing
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 100
AND order_date >= '2024-01-01';
-- Before index: Seq Scan (slow, reads every row)
-- After index: Index Scan (fast, jumps directly)Index Killers — What Prevents Index Usage
Your index exists but isn't being used? These patterns force the database to ignore indexes:
💡 Pro Tip
The golden rule: keep the indexed column clean on one side of the comparison. Don't wrap it in functions, don't convert its type, and avoid leading wildcards.
Index Killers & Fixes
Common mistakes that prevent indexes from being used
-- Bad: function on indexed column kills the index
SELECT * FROM orders
WHERE YEAR(order_date) = 2024; -- can't use index!
-- Good: rewrite to keep column clean
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01'; -- uses index!
-- Bad: implicit type conversion
SELECT * FROM users WHERE phone = 5551234;
-- phone is VARCHAR but you passed an INT
-- Good: match the type
SELECT * FROM users WHERE phone = '5551234';
-- Bad: leading wildcard
SELECT * FROM produ
...Advanced Index Types
Covering indexes and partial indexes for maximum performance
-- Covering index: includes all needed columns
CREATE INDEX idx_orders_covering
ON orders(customer_id, order_date, total);
-- Query uses index-only scan (no table access!)
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 100;
-- Partial index: only index a subset of rows
CREATE INDEX idx_active_users
ON users(email)
WHERE active = true;
-- Only 10% of users are active — index is tiny & fast
SELECT email FROM users WHERE active = true;📘 Quick Reference
| Command | Purpose |
|---|---|
| CREATE INDEX name ON t(col) | Add an index |
| CREATE INDEX ON t(a, b) | Composite index |
| CREATE UNIQUE INDEX | Index with uniqueness |
| EXPLAIN ANALYZE SELECT ... | Show execution plan + timing |
| DROP INDEX name | Remove an index |
🎉 Lesson Complete!
You now understand how to create indexes and read EXPLAIN plans to optimize queries. Next, you'll learn about Views and Stored Procedures for reusable database logic!
Sign up for free to track which lessons you've completed and get learning reminders.