Most application developers treat the database as a black box. They write a query, it returns rows, and as long as the page loads in under a second nobody asks questions. Then traffic grows, the table crosses ten million rows, and the same query that used to be invisible in the trace suddenly dominates every request. The fix is almost always an index, but the developers reaching for it often do not understand what an index actually is or why their first attempt makes things worse instead of better.
I have spent most of my career in regulated fintech, where slow queries are not just a user-experience problem. A ledger read that times out under load can stall a settlement batch, delay a reconciliation, or trip a circuit breaker that pages someone at three in the morning. Indexing is one of the highest-leverage skills an application developer can learn, and it does not require becoming a database administrator. This is the practical mental model I wish every engineer on my teams started with.
What an Index Actually Is
An index is a separate, ordered copy of one or more columns from your table, maintained alongside the table itself. The classic analogy is the index at the back of a book: instead of scanning every page to find each mention of a term, you look up the term in a sorted list and jump straight to the page numbers. A database index works the same way. Without it, the engine performs a table scan, reading every row to find the ones that match. With it, the engine navigates a sorted structure and reads only the rows it needs.
The structure underneath almost every relational index is a B-tree, a balanced tree that keeps data sorted and allows lookups, range scans, and inserts in logarithmic time. That logarithmic behavior is the whole point. On a table of ten million rows, a full scan touches ten million rows; a B-tree lookup touches roughly the depth of the tree, which is typically three or four levels. The difference is not a percentage improvement, it is a change in the shape of the cost curve as your data grows.
The trade-off is that this ordered copy must be kept in sync. Every insert, update, or delete that touches an indexed column also has to update the index. An index is therefore not free: you are buying faster reads with slower writes and more storage. Understanding that bargain is the foundation of every decision that follows.
Reading an Execution Plan
You cannot reason about indexing by staring at SQL. The query optimizer decides how to execute your statement, and the only way to know what it actually did is to read the execution plan. In SQL Server you run a statement with the actual execution plan enabled; in PostgreSQL you prefix it with EXPLAIN ANALYZE. The plan tells you whether the engine used a seek, a scan, what it estimated versus what it actually got, and where the time went.
The single most useful thing to look for is the difference between a seek and a scan. A seek means the engine navigated the B-tree directly to the rows it wanted. A scan means it read the whole structure. A scan is not always wrong, reading an entire small lookup table is fine, but a scan on a large table inside a hot query is usually the smoking gun. The second thing to watch is the gap between estimated and actual row counts. When the optimizer expects ten rows and gets ten thousand, it has likely chosen a bad strategy because its statistics are stale or its assumptions are off.
If you are guessing about which index to add, you are not doing performance work, you are gambling. Read the plan first, form a hypothesis, change one thing, and measure again.
Composite Indexes and Column Order
Most real queries filter on more than one column, which means most useful indexes cover more than one column. The order of those columns is not cosmetic; it is the single most misunderstood part of indexing. A composite index is sorted left to right, like a phone book sorted by last name then first name. You can find everyone named Fowel efficiently, and you can find Fowel, Anselm efficiently, but that same book is useless for finding everyone whose first name is Anselm regardless of surname.
The practical rule I teach is to put equality predicates first, then the column you range over or sort by. If you filter on tenant_id with an exact match and then range over created_at, the index should be on (tenant_id, created_at) in that order. Reverse it and the engine cannot use the leading column to narrow the search, because the rows for one tenant are scattered throughout the structure.
- Lead with columns used in equality filters, since they slice the index cleanly.
- Follow with the column used for ranges, ordering, or grouping, because the index is already sorted on it within each equality group.
- Put high-selectivity columns earlier when several are equally valid, so each level of the tree eliminates as many rows as possible.
- Do not add a column to an index just because it appears in the query; add it because it changes the plan.
Selectivity and Why It Decides Everything
Selectivity is the fraction of rows a predicate eliminates. A column like email is highly selective because each value matches roughly one row. A column like is_active is barely selective because half the table might be active. The optimizer cares deeply about this, because an index is only worth using when it lets the engine skip most of the table. If a query on is_active would return forty percent of the rows, the engine will correctly ignore your index and scan, because jumping back and forth between the index and the table for four million rows is slower than reading the table once in order.
This is why developers are sometimes baffled that their carefully built index goes unused. The index exists, the column is in the WHERE clause, and the optimizer still scans. Usually the answer is that the predicate is not selective enough to justify the index for that particular value, and the engine made the right call. The fix is not to force the index; it is to design a more selective index, often a composite one that combines the weak predicate with a strong one.
Covering Indexes and the Lookup Tax
When the engine uses a non-clustered index to find rows, it gets the indexed columns plus a pointer back to the full row. If your query needs columns that are not in the index, the engine performs a key lookup for each matching row to fetch the rest. For a handful of rows this is invisible. For thousands of rows it can cost more than a scan, and you will see the optimizer abandon the index entirely.
A covering index includes every column the query needs, so the engine never touches the base table at all. In SQL Server you add non-filtering columns with the INCLUDE clause, which stores them at the leaf level of the index without making them part of the sort key. The query is then satisfied entirely from the index, which is often the difference between a query that scales and one that does not.
-- A covering index for a hot dashboard query.
-- Filter and sort columns form the key; display
-- columns ride along in INCLUDE so there is no lookup.
CREATE NONCLUSTERED INDEX IX_Payments_Tenant_Created
ON dbo.Payments (TenantId, CreatedAtUtc DESC)
INCLUDE (Amount, Currency, Status);
-- The query the index is designed to serve:
SELECT Top (50) Amount, Currency, Status, CreatedAtUtc
FROM dbo.Payments
WHERE TenantId = @tenantId
AND CreatedAtUtc >= @from
AND CreatedAtUtc < @to
ORDER BY CreatedAtUtc DESC;The Cost on the Write Path
Every index you add is a tax on every write. Insert a payment row into a table with six indexes and the engine writes seven structures, not one. On a high-throughput ingestion table, that overhead is real and measurable. I have seen teams index defensively, adding one for every query someone might run, and then wonder why their batch inserts crawled. The indexes that made the read dashboards fast were quietly throttling the pipeline that fed them.
This is the discipline that separates competent indexing from cargo-cult indexing. Before adding an index, ask what it costs on the write side and whether the read it accelerates is actually hot. A query that runs once a night during a report rarely justifies slowing down a table that ingests thousands of rows per second. Regulated systems make this even sharper, because the audit and ledger tables are often append-heavy by design, and write latency there is part of your settlement timing.
Indexes also fragment and their statistics drift as data changes. A healthy indexing strategy includes maintenance: rebuilding or reorganizing fragmented indexes and keeping statistics current so the optimizer keeps making good choices. Neglect this and a perfectly good index slowly degrades into a query that the optimizer no longer trusts.
How ORMs Hide the Truth
Most application developers never write the SQL their database runs; an ORM writes it for them. Entity Framework and its peers are excellent at productivity and dangerous at scale, because they make it trivial to generate queries whose cost is invisible at the call site. A LINQ expression that looks like a single line can translate into a join across five tables, a correlated subquery, or the infamous N plus one pattern where one parent query spawns a separate child query per row.
The defense is to capture the generated SQL in development and read its plan as if you had written it by hand. In Entity Framework Core you can log the SQL to the console and feed the hot ones into your database tooling. The point is not to abandon the ORM; it is to remember that the abstraction does not exempt you from understanding what hits the disk.
// Log the SQL EF Core actually generates so you can
// take the hot queries into the plan analyzer.
optionsBuilder
.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging(); // dev only, never in prod
// This innocent expression can become an N+1 storm:
var summaries = db.Tenants
.Where(t => t.Region == region)
.Select(t => new {
t.Name,
PaymentCount = t.Payments.Count() // watch the plan
})
.ToList();A Pragmatic Workflow
I do not want my engineers indexing by intuition, and I do not want them paralyzed by theory either. The workflow that holds up in practice is simple and repeatable. Find the slow query from real telemetry rather than guesswork, because the query you assume is slow is rarely the one burning your CPU. Capture its execution plan, identify the scan or the lookup that dominates the cost, and form a specific hypothesis about which index would change the plan.
Then make one change, measure again on production-like data volumes, and keep the index only if the plan improved and the write cost is acceptable. Production-like volume matters more than anything, because an index decision that looks fine on ten thousand rows can be exactly wrong on ten million. The optimizer behaves differently at scale, and so should your judgment.
Treat indexes as code. Put them in migrations, review them, and revisit them when query patterns change. An index that was essential a year ago may now serve a query that no longer runs, in which case it is pure write overhead and should be dropped. The instinct to add is strong; the discipline to remove is rarer and just as valuable.
Conclusion
Database indexing is not a dark art reserved for specialists. It is a small set of durable ideas: an index is an ordered copy that trades write cost for read speed, column order follows your predicates, selectivity decides whether the optimizer bothers, and the only honest way to evaluate any of it is to read the execution plan against realistic data. Master those and you will resolve the overwhelming majority of the slow queries you encounter without ever filing a ticket for the database team. The black box stops being a black box, and that clarity is worth far more than any single index you will ever add.