Database Performance for Developers: Indexing, Query Plans, and the Queries That Don't Scale

The Query That Worked Fine Until It Didn't

Every developer has a story about the query that worked perfectly in development and crawled to a halt in production. The table had 10,000 rows in your local database; in production it has 10 million. The query that took 5 milliseconds now takes 45 seconds. The dashboard times out. The support tickets arrive.

The culprit is almost always the same: a full table scan. The database engine reads every single row in the table, one by one, looking for the ones that match your WHERE clause. On a table with 10 million rows, that means reading millions of disk pages — work that could have been avoided with the right index[postgresql-docs-indexes].

An index is a separate data structure that the database maintains alongside your table. It works like the index at the back of a textbook: instead of reading every page to find every mention of "normalization," you look up the term in the index, which points you to pages 42, 87, and 156. Database indexes do the same thing, but with a twist that catches many developers off guard: an index is only useful if the query planner decides to use it. And the planner's decision depends on a single number: selectivity[use-the-index-luke].

This guide explains how indexes actually work, when they help, when they hurt, and how to read an EXPLAIN plan to understand what your database is doing — so the next query that doesn't scale is one you catch before it reaches production.

How a B-Tree Index Works

Most database indexes are implemented as B-trees (or their variant, the B+-tree). A B-tree is a self-balancing tree structure that keeps data sorted and allows searches, insertions, and deletions in logarithmic time[date-database-design].

Imagine a table with 1 million rows and an index on the email column. Without an index, finding a specific email requires scanning all 1 million rows. With a B-tree index, the database starts at the root node, compares the target value to the keys in the node, and follows the appropriate branch — roughly 20 comparisons for 1 million rows (log₂ of 1,000,000 ≈ 20), each reading one disk page. The difference between reading 1 million pages and 20 pages is the difference between a query that takes seconds and one that takes milliseconds[use-the-index-luke].

The B-tree structure also explains why indexes are sorted: range queries (WHERE age BETWEEN 25 AND 35) can find the starting point in the tree and then scan sequentially through the leaf nodes. A hash index, by contrast, supports only equality lookups — fast for WHERE email = 'x' but useless for ranges[postgresql-docs-indexes].

The cost of indexing is not free. Every index you add slows down INSERT, UPDATE, and DELETE operations because the database must update both the table and every affected index. A table with 5 indexes takes roughly 5× longer to insert into than a table with none. The art of indexing is balancing read speed against write speed — and knowing when an index helps so little that it isn't worth the cost.

Selectivity: The Number That Decides Everything

Selectivity is the fraction of rows a filter keeps. A filter WHERE email = 'a@b.com' on a unique column has selectivity ≈ 1/N (one row out of N). A filter WHERE status = 'active' on a column with two values has selectivity ≈ 0.5 (half the rows). The query planner uses this number to decide: is it cheaper to read the index and then fetch the matching rows, or just scan the whole table?[use-the-index-luke].

The formula for selectivity is straightforward:

where R is the number of rows returned and N is the total rows in the table. A selectivity of 0.00001 (one row in 100,000) means the index is almost certainly used. A selectivity of 0.5 (half the rows) means the planner will likely ignore the index and scan the table directly — reading 500,000 rows via index lookups is more expensive than reading them sequentially[postgresql-docs-indexes].

The SQL Index Selectivity Calculator computes this from your table's row count, the number of distinct values in the indexed column, and the rows your query returns. It also reports the planner's likely choice (index scan vs sequential scan) and the estimated cost of each — useful for deciding whether an index on a given column will actually be used.

The cliff is steep. Going from 100 distinct values (1% selectivity, clearly indexed) to 10 distinct values (10% selectivity, borderline) multiplies selectivity by 10×. Below 100 distinct values on a large table, a single-column index is questionable; above 1,000 it is almost always worthwhile[use-the-index-luke].

Composite Indexes: Order Matters

A composite index (also called a multi-column index) indexes two or more columns together. This is where most developers make their first indexing mistake: they assume the column order doesn't matter. It does.

A composite index on (last_name, first_name) can answer queries that filter on last_name alone, or on both last_name AND first_name. But it cannot answer a query that filters on first_name alone. The reason is the B-tree structure: the tree is sorted by last_name first, then by first_name within each last_name. Searching for first_name = 'John' without knowing the last_name is like searching for everyone named "John" in a phone book sorted by last name — you have to read the whole book[use-the-index-luke].

The rule: put the most selective column first (the one that eliminates the most rows), and put columns used in equality filters before columns used in range filters. A query WHERE status = 'active' AND created_at > '2024-01-01' benefits from an index on (status, created_at) — equality first, range second. Reversing the order to (created_at, status) is less efficient because the planner can use the first column for range scanning but must filter the second column from the results[postgresql-docs-indexes].

Practical example: A users table with 5 million rows, 50,000 distinct cities, and 2 statuses (active/inactive). A query WHERE city = 'Berlin' AND status = 'active':

• Index on (city, status): selectivity ≈ 1/50,000 × 0.5 = 0.00001 → index used, ~100 rows fetched
• Index on (status, city): selectivity ≈ 0.5 × 1/50,000 = 0.00001 → same final result, but the planner may prefer the more selective column first

The difference becomes dramatic when the first column is not selective. An index on (status, city) forces the planner to scan all 2.5 million active users first, then filter by city. An index on (city, status) jumps directly to Berlin's ~100 users, then filters by status[use-the-index-luke].

Covering Indexes: When the Index Is the Answer

A covering index is an index that contains all the columns a query needs, so the database can answer the query entirely from the index without ever touching the table. This is the fastest possible access path[postgresql-docs-indexes].

Consider a query SELECT user_id, email FROM users WHERE email = 'a@b.com'. With an index on (email), the database finds the row in the index, then fetches the corresponding table row to get user_id. With a covering index on (email, user_id), the database finds everything it needs in the index — no table access at all. On a large table, this can be 10-100× faster because the index is smaller and more cacheable than the table[schwartz-highperf-mysql].

The tradeoff: covering indexes are wider (more columns = more bytes per entry), which means fewer entries per disk page and more storage. Use them for your most frequent queries, not for every query. A good candidate is a query that runs thousands of times per minute and returns few columns.

PostgreSQL's INCLUDE clause (available since PostgreSQL 11) lets you add non-key columns to an index without making them part of the sort key: CREATE INDEX ON users (email) INCLUDE (user_id). This gives you the covering benefit without the sorting overhead on the included columns[postgresql-docs-indexes].

Reading EXPLAIN: What the Planner Is Actually Doing

Every database provides an EXPLAIN command that shows the query plan — the step-by-step strategy the planner has chosen. Learning to read these plans is the single most useful skill for database performance[postgresql-docs-indexes].

A typical PostgreSQL EXPLAIN output for a query on a 10-million-row table:

Seq Scan on users  (cost=0.00..183340.00 rows=1 width=36)
  Filter: (email = 'a@b.com'::text)

This tells you: the planner chose a sequential scan (reading every row), estimated cost is 183,340 (arbitrary units), expects to return 1 row, and applies the email filter to each row. The cost number is what the planner uses to compare strategies — lower is better[postgresql-docs-indexes].

After adding an index on email:

Index Scan using users_email_idx on users  (cost=0.43..8.45 rows=1 width=36)
  Index Cond: (email = 'a@b.com'::text)

Now the planner uses the index, and the estimated cost dropped from 183,340 to 8.45 — a 20,000× improvement. The Index Cond shows what the index is filtering on[postgresql-docs-indexes].

Key terms in EXPLAIN output:

The rows= estimate is critical. If the planner estimates 1 row but the query actually returns 100,000, it may have chosen a nested loop when a hash join would have been faster. Run ANALYZE after bulk loads to update statistics — stale statistics are the most common cause of bad plans[postgresql-docs-indexes].

When Indexes Don't Help

Indexes are not a performance panacea. They fail to help — or actively hurt — in several common scenarios:

Low cardinality columns. An index on a boolean column (2 distinct values) is almost never used. The planner would have to read half the table via index lookups, which is more expensive than a sequential scan. The same applies to any column where selectivity exceeds roughly 10-20%[use-the-index-luke].

Functions on indexed columns. WHERE LOWER(email) = 'a@b.com' cannot use a regular index on email because the index stores the original values, not the lowercased versions. The fix is a functional index: CREATE INDEX ON users (LOWER(email)). Similarly, WHERE date(created_at) = '2024-01-01' cannot use an index on created_at; rewrite as WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' instead[postgresql-docs-indexes].

Implicit type casting. WHERE int_column = '42' (string literal) may prevent index use because the database must cast every row. Match types exactly: WHERE int_column = 42.

Leading wildcards. WHERE email LIKE '%@gmail.com' cannot use a B-tree index because the index is sorted from the left. WHERE email LIKE 'john%' can use the index; WHERE email LIKE '%john%' cannot. For substring search, consider full-text search indexes (GIN in PostgreSQL) instead[postgresql-docs-indexes].

Too many indexes. Each index slows writes. A table with 10 indexes takes roughly 10× longer to insert into than a table with none. If your workload is write-heavy (logging, event tracking), be stingy with indexes. If it's read-heavy (analytics, dashboards), be generous[schwartz-highperf-mysql].

A Worked Example: The Slow Dashboard

A team reports that their admin dashboard takes 8 seconds to load. The query:

SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
  AND u.created_at > '2024-01-01'
  AND o.total > 100
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 20;

The users table has 5 million rows; orders has 50 million. EXPLAIN shows:

Sort  (cost=452310.00..452310.50 rows=20 width=52)
  Sort Key: (count(o.id)) DESC
  ->  HashAggregate  (cost=452300.00..452305.00 rows=20 width=52)
        Group Key: u.id
        ->  Hash Join  (cost=125000.00..327300.00 rows=2500000 width=44)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders  (cost=0.00..150000.00 rows=10000000 width=12)
                    Filter: (total > 100)
              ->  Hash  (cost=100000.00..100000.00 rows=2500000 width=36)
                    ->  Seq Scan on users  (cost=0.00..100000.00 rows=2500000 width=36)
                          Filter: ((status = 'active') AND (created_at > '2024-01-01'))

Both tables are being fully scanned. The plan reads 2.5 million users and 10 million orders, joins them, groups, sorts, then returns 20 rows. The fix:

1. CREATE INDEX ON users (status, created_at) INCLUDE (name, email); — covering index for the user filter
2. CREATE INDEX ON orders (user_id) WHERE total > 100; — partial index for the order filter

After indexing, EXPLAIN shows:

Limit  (cost=0.85..1250.50 rows=20 width=52)
  ->  Sort  (cost=0.85..1250.50 rows=5000 width=52)
        Sort Key: (count(o.id)) DESC
        ->  GroupAggregate  (cost=0.85..1000.00 rows=5000 width=52)
              Group Key: u.id
              ->  Nested Loop  (cost=0.85..800.00 rows=5000 width=44)
                    ->  Index Only Scan using users_status_created_at_idx on users
                          Index Cond: ((status = 'active') AND (created_at > '2024-01-01'))
                    ->  Index Scan on orders_user_id_idx on orders
                          Index Cond: (user_id = u.id)

Cost dropped from 452,310 to 1,250 — a 360× improvement. The dashboard loads in 20ms instead of 8 seconds. The key insight: the planner now reads only the ~5,000 active recent users (via the covering index) and joins only their orders (via the partial index), instead of scanning both tables entirely[postgresql-docs-indexes].

Practical Tips for Database Performance

1. Index columns in WHERE, JOIN, and ORDER BY clauses. These are the columns the planner needs to filter, join, or sort on.
2. Put the most selective column first in composite indexes. The column that eliminates the most rows should be leftmost.
3. Use covering indexes for hot queries. If a query runs thousands of times per minute, the extra storage is worth the speed.
4. Run ANALYZE after bulk loads. Stale statistics cause bad plans. PostgreSQL auto-analyzes, but large bulk loads may need a manual ANALYZE.
5. Use partial indexes for filtered queries. CREATE INDEX ... WHERE total > 100 is smaller and faster than indexing the whole table.
6. Don't index everything. Each index slows writes. A table with 10 indexes takes roughly 10× longer to insert into than a table with none.
7. Read EXPLAIN before deploying. If you see Seq Scan on a large table, investigate. The planner is telling you it has no better option.
8. Check selectivity before creating an index. If the column has fewer than ~100 distinct values on a large table, the index will likely be ignored. The SQL Index Selectivity Calculator tells you whether the planner will use it.

Limitations and When to Go Beyond Indexing

Indexing is necessary but not sufficient for database performance at scale. When indexes alone aren't enough:

• Query rewriting. A query that uses a subquery may be slower than one that uses a join, even with identical indexes. EXPLAIN reveals the difference.
• Denormalization. For read-heavy analytics, duplicating data (storing order_count on the user row instead of computing it) trades write complexity for read speed.
• Partitioning. Splitting a 500-million-row table into partitions by date lets the planner skip entire partitions — a 100× reduction in data scanned for time-bounded queries.
• Read replicas. For workloads with many more reads than writes, replicas distribute the read load across multiple servers.
• Caching. Redis or application-level caching avoids the database entirely for frequently accessed, rarely changed data.

Indexing is the first line of defense, not the last. But it is the one that catches the most common performance problems — and the one that most developers underuse because they've never learned to read the planner's output.

Frequently Asked Questions

❓ What is a database index?

✅ A separate data structure (usually a B-tree) that the database maintains alongside your table. It allows the database to find rows without scanning the entire table, similar to an index at the back of a textbook.

❓ Why isn't my index being used?

✅ Most often because selectivity is too high — the filter keeps too many rows (roughly more than 10-20% of the table), so the planner decides a sequential scan is cheaper. Other causes: functions on the indexed column (WHERE LOWER(col) = ...), implicit type casting, or stale statistics.

❓ What is selectivity?

✅ The fraction of rows a filter keeps: rows returned divided by total rows. Low selectivity (few rows) means the index is useful. High selectivity (many rows) means the planner will likely ignore the index.

❓ What is a composite index?

✅ An index on two or more columns. Order matters: a composite index on (A, B) can answer queries filtering on A alone or A and B, but not B alone. Put the most selective column first.

❓ What is a covering index?

✅ An index that contains all the columns a query needs, so the database can answer the query from the index alone without fetching table rows. This is the fastest access path for a query.

❓ How do I read an EXPLAIN plan?

✅ Look for Seq Scan (full table scan — bad on large tables), Index Scan (index used — good), and Index Only Scan (covering index — best). The cost number lets you compare strategies; the rows estimate tells you how accurate the planner's guess is.

❓ Does every column need an index?

✅ No. Each index slows down INSERT, UPDATE, and DELETE operations. Index columns used in WHERE, JOIN, and ORDER BY clauses, and be skeptical of columns with fewer than ~100 distinct values on large tables.

❓ What is a partial index?

✅ An index with a WHERE clause: CREATE INDEX ... WHERE total > 100. It indexes only the rows matching the condition, making it smaller and faster than a full-table index. Useful for queries that always filter on the same condition.