Slow queries are almost always an indexing problem. This lesson teaches you exactly how B-tree indexes work, how to choose the right columns to index, and how to verify that your indexes are actually being used — with real SQL examples from a production-scale table.
You've written a query that looks perfectly reasonable — a JOIN on two tables, a WHERE clause filtering by date and customer ID, maybe an ORDER BY at the end. You run it expecting results in milliseconds. Instead, you watch the spinner turn for 12 seconds. You run it again. Same result. Welcome to the moment every data professional eventually faces: your query isn't slow because it's wrong, it's slow because the database is doing far more work than it needs to.
The fix is almost always indexes. An index is a separate data structure that the database maintains alongside your table — a pre-organized lookup that lets the query engine jump directly to the rows you need, instead of scanning every single row from top to bottom. Think of it like the index at the back of a textbook. You could find every mention of "cohort analysis" by reading every page from cover to cover, or you could flip to the index, find the page numbers in two seconds, and go directly there. Databases make the same choice, and your job as a SQL practitioner is to give them good indexes to work with.
By the end of this lesson, you'll understand how indexes actually work under the hood, know how to choose the right columns to index, be able to create single-column and multi-column indexes confidently, and use execution plans to verify whether your indexes are actually being used.
What you'll learn:
You should be comfortable writing SELECT queries with WHERE clauses, JOINs, and ORDER BY. Familiarity with basic table structure (columns, data types, primary keys) is assumed. If you want to go deeper on execution plans alongside this lesson, the article on query profiling and statistics in SQL: using EXPLAIN ANALYZE, buffer metrics, and row estimates to diagnose slow queries is an excellent companion. You do not need any prior knowledge of indexing.
Most indexes you'll encounter are B-tree indexes — short for "balanced tree." Understanding this structure, even at a high level, will make every indexing decision you make more intuitive.
When you create an index on a column, the database builds a tree data structure where each node contains a range of sorted values from that column and pointers to the next level down. At the bottom of the tree are the "leaf nodes," which contain the actual indexed values and pointers to the physical rows in your table. The tree is "balanced" because every path from the root to a leaf is the same depth — which means lookups are consistently fast regardless of where in the alphabet or number range your value falls.
Here's what this means practically: if you have a customers table with 10 million rows and you search for WHERE customer_id = 4823901, the database traverses the B-tree in roughly log₂(10,000,000) ≈ 23 steps, rather than reading all 10 million rows. That's the difference between a few microseconds and several seconds.
B-tree indexes also support range queries efficiently. A query like WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31' can find the starting leaf node for January 1st and then walk forward through the sorted structure until it hits the end of March. This is why B-trees work well for equality lookups (=), range filters (>, <, BETWEEN), and sorting (ORDER BY).
Note: Most databases (PostgreSQL, MySQL, SQL Server, SQLite) default to B-tree when you create an index without specifying a type. Other index types exist — hash indexes for pure equality, GIN/GiST for full-text and array search — but B-tree covers 95% of everyday use cases, and it's what we'll focus on here.
The most common mistake beginners make is indexing columns randomly, or indexing every column "just in case." Indexes are not free — they consume disk space and slow down INSERT, UPDATE, and DELETE operations because the database has to maintain the index alongside the table. You want to be deliberate.
Here's a practical framework for identifying good index candidates:
1. Columns in WHERE clauses that filter significantly
If a query filters by status = 'active' and 98% of your rows are active, an index on status is nearly useless — the database will scan the table anyway because it's faster. But if you filter by account_manager_id and each account manager owns 0.1% of rows, an index can narrow the result set dramatically. Look for columns with high cardinality — many distinct values relative to total rows.
2. Columns used in JOIN conditions
When you JOIN orders to customers on orders.customer_id = customers.id, the database needs to look up each customer_id in the customers table. An index on orders.customer_id (the foreign key side) is almost always beneficial. Primary keys are indexed automatically; foreign keys often are not — this is one of the most overlooked performance wins.
3. Columns used in ORDER BY and GROUP BY
Sorting is expensive. If your queries frequently sort by created_at or group by region, an index on those columns can let the database retrieve rows in pre-sorted order, skipping the sort step entirely.
4. Columns used in high-frequency, latency-sensitive queries A query that runs once a month for a report can afford a 10-second runtime. A query that runs 500 times per second on your application's main API endpoint cannot. Prioritize indexes based on query frequency and business impact.
Warning: Don't index every column in your table. A table with 15 indexes is a table where every INSERT takes 15x the write work. For write-heavy tables like event logs or transaction records, be especially conservative. Index the columns that serve your most critical read queries, and profile before adding more.
Let's work with a realistic scenario. We have an e-commerce database with an orders table:
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
region VARCHAR(50) NOT NULL,
order_total DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP NOT NULL,
shipped_at TIMESTAMP
);
This table has 8 million rows. Let's look at a query that's running slowly:
SELECT order_id, customer_id, order_total
FROM orders
WHERE customer_id = 10042
AND status = 'shipped'
ORDER BY created_at DESC;
Without any index (other than the primary key on order_id), this query does a full table scan — reading all 8 million rows to find the ones belonging to customer 10042.
The simplest fix is a single-column index on customer_id:
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
The naming convention idx_[table]_[columns] isn't required, but it's a good habit — you'll thank yourself later when scanning a list of 40 indexes. Now the query can jump directly to customer 10042's rows.
But notice our WHERE clause filters on both customer_id AND status. A composite index on both columns can be even more efficient:
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);
This index stores rows sorted first by customer_id, then by status within each customer. The query can now locate customer 10042's rows and immediately filter to 'shipped' status within the index itself, before touching the main table.
Key insight: Column order in a composite index matters enormously. The index
(customer_id, status)can serve queries that filter oncustomer_idalone, or oncustomer_idANDstatustogether. It cannot efficiently serve queries that filter onstatusalone — because the index is sorted by customer_id first, and status values are scattered across all customers. This is called the "leftmost prefix rule": the index is usable starting from the leftmost column, and stops being usable as soon as you skip a column.
Here's a performance trick that takes things one step further: a covering index includes all the columns that a query needs, so the database never has to touch the main table at all. It finds everything it needs in the index structure itself.
For our example query, which selects order_id, customer_id, and order_total, and filters on customer_id and status, and orders by created_at:
CREATE INDEX idx_orders_covering
ON orders (customer_id, status, created_at DESC, order_total)
INCLUDE (order_id);
Note: The
INCLUDEclause (available in PostgreSQL and SQL Server) lets you add non-key columns to the leaf nodes of the index without affecting the sort order. MySQL achieves similar results by simply adding extra columns to the index definition. When all columns needed by a query exist in the index, the query plan will show an "Index Only Scan" — one of the fastest operations possible.
Sometimes you only care about a subset of rows. If 95% of your orders have status = 'delivered' and your performance-critical queries only ever look at status = 'pending', you can create an index that covers only pending orders:
CREATE INDEX idx_orders_pending
ON orders (customer_id, created_at)
WHERE status = 'pending';
This index is much smaller than a full index — it only contains rows where the condition is true — which means it fits more easily in memory and updates faster. Partial indexes are underused and often dramatically effective for tables where queries cluster around a specific subset.
Creating an index and actually using an index are two different things. The database's query optimizer decides whether to use your index based on statistics about the data. You need to verify.
In PostgreSQL, prepend EXPLAIN ANALYZE to your query:
EXPLAIN ANALYZE
SELECT order_id, customer_id, order_total
FROM orders
WHERE customer_id = 10042
AND status = 'shipped'
ORDER BY created_at DESC;
Before adding the index, you'd see something like:
Seq Scan on orders (cost=0.00..245000.00 rows=47 width=28)
(actual time=0.842..11423.551 rows=47 loops=1)
Filter: ((customer_id = 10042) AND (status = 'shipped'))
Rows Removed by Filter: 7999953
"Seq Scan" means sequential (full table) scan. Nearly 8 million rows were read and discarded to return 47.
After creating the composite index, you'd see:
Index Scan using idx_orders_customer_status on orders
(cost=0.56..312.44 rows=47 width=28)
(actual time=0.034..1.847 rows=47 loops=1)
Index Cond: ((customer_id = 10042) AND (status = 'shipped'))
Total time dropped from 11,423ms to 1.8ms. "Index Scan" confirms the optimizer chose your index. For deeper guidance on interpreting these execution plans, see the lesson on reading execution plans for advanced performance analysis.
Tip: If you create an index but the query plan still shows a Seq Scan, the most common reasons are: (1) the table is small enough that a scan is cheaper, (2) the column's cardinality is too low to make the index useful, (3) your query has a function wrapping the indexed column (like
WHERE LOWER(email) = 'test@example.com'won't use an index onANALYZE orders;in PostgreSQL to refresh statistics.
Speaking of functions — this is a common trap. You have an index on email, but your query looks like this:
SELECT customer_id, email
FROM customers
WHERE LOWER(email) = LOWER('Alice@Example.com');
The database can't use your index on email because the indexed values are mixed-case, but you're searching on the lowercased version. The solution is a functional index (also called an expression index):
CREATE INDEX idx_customers_email_lower
ON customers (LOWER(email));
Now the index stores the lowercased version, and the query that searches on LOWER(email) can use it. This same technique applies to any deterministic expression — extracting the year from a timestamp, computing a hash, trimming whitespace.
This matters especially when you're working with string filtering patterns like LIKE and REGEXP, where case-insensitive searches are common.
Understanding when indexes fail to help is as important as knowing when they do. Here are the most common query patterns that prevent index usage:
Leading wildcard LIKE: WHERE description LIKE '%invoice%' cannot use a B-tree index because the wildcard is at the start — the index has no way to narrow down where values with "invoice" somewhere in the middle begin. WHERE description LIKE 'invoice%' (wildcard only at the end) can use an index because it's equivalent to a range scan.
Type mismatches: If customer_id is stored as a BIGINT but your query uses WHERE customer_id = '10042' (a string literal), the database may cast values to compare them — which defeats index usage. Match your literal types to your column types.
OR conditions across different columns: WHERE customer_id = 10042 OR region = 'EU' is harder for the optimizer to satisfy with a single index. You may need two separate indexes and a query rewrite, or to use UNION to split the OR into two index-friendly queries.
Non-deterministic functions: WHERE created_at > NOW() - INTERVAL '30 days' is fine (NOW() is evaluated once per query). But some function combinations can surprise the optimizer into not using indexes — always verify with EXPLAIN.
This class of performance-killing patterns is explored in much more depth in the lesson on advanced SQL anti-patterns that kill performance at scale.
Let's talk about write performance. Every index you create must be maintained. When a row is inserted into orders, the database inserts that row into the main table and updates every index that covers any column in that row. If you have 8 indexes on orders and you bulk-load 2 million new rows, you're doing 2 million × 8 = 16 million index insertions.
You can audit your existing indexes in PostgreSQL with:
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan ASC;
The idx_scan column tells you how many times each index has been used since the last statistics reset. An index with idx_scan = 0 after weeks of production traffic is a strong candidate for removal — it's paying write costs but delivering zero read benefit.
Warning: Before dropping an index in production, make sure you understand what queries it was intended to serve. Some indexes are only hit during monthly batch jobs, quarterly reports, or emergency incident queries. Check with your team and look at historical query logs before removing anything.
Work through these steps using the orders table schema from earlier in this lesson. If you have a local PostgreSQL or MySQL instance, create the table and load some test data (even a few hundred thousand rows of fake data will demonstrate the concepts).
Step 1: Write a query that retrieves all orders for a specific region where order_total > 500, ordered by created_at descending. Run it with EXPLAIN ANALYZE and note whether it uses a Seq Scan.
Step 2: Create a single-column index on region. Re-run the EXPLAIN. Did the plan change? Why or why not? (Hint: think about cardinality — how many distinct regions are there?)
Step 3: Create a composite index on (region, order_total). Re-run the EXPLAIN. Does the plan now use the index for both the filter and the range scan on order_total?
Step 4: Extend the index to also include created_at as a third key column: (region, order_total, created_at DESC). Check if the sort step disappears from the execution plan.
Step 5: Simulate a partial index scenario: create an index only for rows where status = 'pending', and write a query that targets only pending orders by region. Compare its execution plan to the same query without the partial index.
For each step, write down the estimated cost (from EXPLAIN) and the actual execution time. Seeing these numbers change as you add indexes is the most effective way to internalize why indexing decisions matter.
"I created the index but the query is still slow."
First, verify the index exists: \d orders in psql, or query information_schema.statistics in MySQL. Then run EXPLAIN and check whether the optimizer chose your index. If it's doing a Seq Scan, the most likely culprits are stale statistics (ANALYZE the table), low cardinality on the indexed column, or a function wrapping the column in your WHERE clause.
"My composite index isn't helping for some queries."
Check the leftmost prefix rule. If your index is (customer_id, status, created_at), a query filtering on status alone cannot use this index. Either reorder the index columns to match your most common query pattern, or create an additional index starting with status.
"Indexing slowed down my bulk loads." This is expected. For large one-time data loads, a common pattern is to drop indexes before loading and recreate them after. This is far faster than maintaining indexes during the insert. See the lesson on bulk data loading and upsert patterns for more on this pattern.
"I don't know which queries to optimize."
In PostgreSQL, the pg_stat_statements extension tracks query execution statistics over time. Enable it, wait a few days, then query it to find your top queries by total execution time. That's your indexing priority list.
Indexes are the single highest-leverage tool you have for query performance on large tables. The core ideas to carry forward:
pg_stat_user_indexesOnce you're comfortable with indexing fundamentals, the next layer of performance optimization opens up. Partitioning strategies let you divide enormous tables so queries can skip entire partitions wholesale — see the lesson on partitioning strategies and partition pruning for large datasets. For complex analytical queries involving window functions and aggregations, advanced indexing strategies and query rewriting for production systems takes the concepts here into production-grade territory. And if your slow queries involve complex subquery logic, the article on query rewriting with common subexpression elimination and optimizer hints will give you powerful complementary techniques.
Performance optimization is iterative. Measure first, index deliberately, verify with execution plans, and measure again.