A query that takes 30 seconds without an index can run in 30ms with the right index. But the wrong index can make INSERTs 10x slower. In this lesson you'll learn how Postgres' query planner thinks — and write EXPLAIN ANALYZE like a DBA.
Learning Objectives
After this lesson, you will be able to:
Explain what an index is and how B-tree indexes speed up lookups
Create single-column and composite (multi-column) indexes
Use EXPLAIN ANALYZE (Postgres) / EXPLAIN QUERY PLAN (SQLite) to see how the database executes a query
Identify when indexes help and when they hurt performance
Understand covering indexes and how they eliminate table lookups
Apply indexing best practices for common query patterns
Know when to reach for B-tree vs Hash vs GIN vs BRIN (Postgres index types)
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
An index is like a book's table of contents -- it tells you exactly where to find what you need without reading every page.
Without an index, finding the word "B-tree" in a 500-page textbook means reading every page from start to finish (full table scan). With an index in the back of the book, you look up "B-tree," see "page 247," and go directly there (index lookup).
A database index works the same way: it maintains a sorted data structure (usually a B-tree) that maps column values to row locations. Instead of scanning millions of rows, the database traverses the B-tree in O(log n) steps.
If you have written queries that "just work" on small tables, this is where you learn why the same query might crawl on real-world data -- and how to fix it. Indexing is the bridge between toy databases and production systems.
-- Without an index on email, the database reads EVERY row
SELECT * FROM users WHERE email = 'alice@example.com';
-- On 10 million rows, this means:
-- 10,000,000 row comparisons
-- ~5 seconds on a typical disk
-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);
-- Now the same query uses the index
SELECT * FROM users WHERE email = 'alice@example.com';
-- On 10 million rows:
-- ~23 B-tree comparisons (log2 of 10M)
-- ~0.001 seconds
-- Index on a single column
CREATE INDEX idx_employees_name ON employees(name);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_products_category ON products(category);
-- Unique index (enforces uniqueness like a UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Index on multiple columns (order matters!)
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
-- This index helps with:
-- WHERE customer_id = 42 (uses first column)
-- WHERE customer_id = 42 AND order_date > '2024' (uses both columns)
-- ORDER BY customer_id, order_date (matches index order)
-- This index does NOT help with:
-- WHERE order_date > '2024' (skips the first column -- can't use index)
-- Check if a JOIN uses indexes
EXPLAIN QUERY PLAN
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';
-- Output might show:
-- SEARCH departments USING INDEX sqlite_autoindex_departments_1 (name=?)
-- SEARCH employees USING INDEX idx_emp_dept (department_id=?)
-- Both tables use indexes -- good!
-- Check if an ORDER BY needs a temporary sort
EXPLAIN QUERY PLAN
SELECT * FROM employees ORDER BY salary DESC;
-- Output might show:
-- SCAN employees
-- USE TEMP B-TREE FOR ORDER BY
-- No index on salary, so full scan + sort. Consider adding an index.
Try it! Run EXPLAIN QUERY PLAN on different queries and see how the database uses (or does not use) your indexes.
-- 1. WHERE clause filters (the most common use case)
SELECT * FROM users WHERE email = 'alice@example.com';
-- Index on: users(email)
-- 2. JOIN conditions
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- Index on: orders(customer_id) -- the foreign key column
-- 3. ORDER BY (avoids temp B-tree sort)
SELECT * FROM products ORDER BY price DESC;
-- Index on: products(price)
-- 4. GROUP BY
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
-- Index on: employees(department_id)
-- 5. Unique lookups (already indexed if UNIQUE or PRIMARY KEY)
SELECT * FROM users WHERE id = 42;
-- PRIMARY KEY already has an index
-- 1. Tables with very few rows (< 1000)
-- Index overhead is not worth it. Full scan is fast enough.
-- 2. Columns with very low cardinality
CREATE INDEX idx_gender ON users(gender);
-- Only 2-3 distinct values. The index barely narrows the search.
-- 3. Columns that are rarely used in WHERE/JOIN/ORDER BY
CREATE INDEX idx_bio ON users(bio);
-- If you never filter or sort by bio, this index wastes space and slows writes.
-- 4. Tables with heavy write traffic and light read traffic
-- Every INSERT/UPDATE/DELETE must also update every index.
-- On a logging table with 10,000 inserts/second and rare reads, indexes are costly.
A covering index contains ALL the columns needed by a query. The database can answer the query entirely from the index without ever reading the main table. This is the fastest possible query.
sql
-- Query: find names and salaries for a department
SELECT name, salary FROM employees WHERE department_id = 2;
-- Regular index: find rows via index, then look up name and salary from the table
CREATE INDEX idx_dept ON employees(department_id);
-- Two steps: index lookup + table lookup
-- Covering index: includes ALL columns the query needs
CREATE INDEX idx_dept_covering ON employees(department_id, name, salary);
-- One step: everything is in the index. No table lookup needed.
In EXPLAIN output, a covering index shows as:
SEARCH employees USING COVERING INDEX idx_dept_covering (department_id=?)
The word "COVERING" means zero table lookups -- maximum speed.
Covering indexes are wider (more columns = more storage). They slow down writes more than narrow indexes. Use them for your most critical read queries, not for everything.
sql
-- This covering index helps this specific query pattern:
CREATE INDEX idx_orders_covering
ON orders(customer_id, order_date, total);
-- Fast query (covered):
SELECT order_date, total FROM orders WHERE customer_id = 42;
-- This query is NOT covered (needs 'status' column, which is not in the index):
SELECT order_date, status FROM orders WHERE customer_id = 42;
-- In development: use EXPLAIN QUERY PLAN on every important query
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42 ORDER BY order_date DESC;
-- Look for: SCAN (full table scan) and USE TEMP B-TREE (sort without index)
-- ALWAYS index foreign key columns (used in JOINs)
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_employees_dept ON employees(department_id);
-- Index columns used in frequent WHERE clauses
CREATE INDEX idx_users_email ON users(email); -- login lookup
CREATE INDEX idx_orders_status ON orders(status); -- filter by status
CREATE INDEX idx_products_category ON products(category); -- category pages
-- Composite index that helps both WHERE and ORDER BY
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date DESC);
-- This query benefits from the index for both filtering AND sorting:
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY order_date DESC
LIMIT 10;
-- Check which indexes exist on a table
PRAGMA index_list('orders');
-- Check which columns an index covers
PRAGMA index_info('idx_orders_customer_date');
-- Check the size of your database (indexes included)
PRAGMA page_count;
PRAGMA page_size;
-- Total size = page_count * page_size bytes
-- BAD: wrapping a column in a function prevents index use
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- The index on email is NOT used because LOWER() transforms the column
-- GOOD: store data normalized, query without functions
SELECT * FROM users WHERE email = 'alice@example.com';
-- Index on email IS used
-- May not use indexes efficiently
SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';
-- Better: use UNION (each sub-query can use its own index)
SELECT * FROM users WHERE email = 'alice@example.com'
UNION
SELECT * FROM users WHERE name = 'Alice';
-- BAD: leading wildcard -- full scan
SELECT * FROM users WHERE name LIKE '%alice%';
-- GOOD: prefix match -- can use index
SELECT * FROM users WHERE name LIKE 'Alice%';
Indexing is one of those skills where a little knowledge goes a long way. Most applications need only 5-10 well-chosen indexes to keep all queries fast. Start with foreign keys, add indexes for your slowest queries, use EXPLAIN QUERY PLAN to verify, and resist the urge to index everything. You now have the tools to diagnose and fix 90% of SQL performance problems.
Quick Check1 / 5
What type of data structure do most SQL indexes use?
What's next: You can now design tables and tune query performance. The next lesson zooms out to data modeling — normalization, star schemas, and ER diagrams. Schema decisions that affect every query you'll ever write.