Can an AI Explain Something Technical? Read Five and Decide

A table stores rows. An index stores selected column values in searchable order, plus a pointer back to the row.

That one sentence explains most index behavior: why reads can get faster, why writes get slower, why column order matters, and why some indexes do nothing useful.

What happens without an index

Suppose you have a table:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  total_cents INTEGER NOT NULL
);

And this query:

SELECT *
FROM orders
WHERE customer_id = 42;

Assume orders has 10,000,000 rows, and customer 42 has 120 orders.

Without an index on customer_id, the database has no direct way to jump to “rows for customer 42.” The rows are stored in data pages on disk and in memory. A page is a fixed-size block — commonly 8 KB or 16 KB depending on the database. Each page contains multiple rows, plus metadata.

To answer the query, the database performs a table scan:

1. Read the first table page.
2. Inspect each row on that page.
3. Evaluate customer_id = 42.
4. Return the row if it matches.
5. Move to the next page.
6. Repeat until the table is exhausted.

Physically, this means the database touches the table’s storage from beginning to end, or at least all pages relevant to the table. If the table occupies 80 GB, the database may need to read a large portion of that 80 GB, unless the pages are already in memory.

A sequential scan is not always bad. It is often very efficient per byte read. Databases are good at reading consecutive pages, prefetching them, and evaluating predicates quickly. If your query needs 80% of the table anyway, a sequential scan may be the best possible plan.

But for the query above, you want 120 rows out of 10,000,000. Reading every row to find them is wasteful. The database needs a structure that narrows the search before visiting the table rows.

That structure is an index.

What structure an index adds

A typical relational database index is a B-tree or B-tree-like structure. You do not need to memorize the algorithms, but you do need the shape:

• The index is separate from the table.
• It is stored in pages too.
• Entries are kept in sorted order by the indexed column or columns.
• Leaf entries contain the indexed key value and a row locator.
• Internal pages guide the database to the correct leaf page.

For an index on customer_id:

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

The index contains entries conceptually like:

customer_id | row pointer
------------+------------
1           | row location A
1           | row location B
2           | row location C
42          | row location D
42          | row location E
42          | row location F
...

The “row pointer” depends on the database. In PostgreSQL, it is a tuple identifier pointing to a table block and row offset. In SQL Server clustered indexes, the locator may be the clustered key. In InnoDB, secondary indexes point to the primary key, which is then used to find the row. The exact implementation varies, but the concept is the same: the index entry tells the database where the actual row can be found.

The reason the structure is sorted is that sorted data can be searched by range. If the index entries are ordered by customer_id, the database can navigate to the first entry where customer_id = 42, then continue forward until the key is no longer 42.

It does not need to inspect entries for customers 1 through 41 one by one. It uses the tree’s internal pages to jump close to the target.

For the earlier query:

SELECT *
FROM orders
WHERE customer_id = 42;

With 10,000,000 rows and 120 matching rows, the work becomes roughly:

1. Read a few index pages to navigate the tree.
2. Find the first index entry for customer_id = 42.
3. Read the nearby index entries for the remaining 119 matches.
4. Use their row locators to fetch the actual table rows.

Instead of scanning 10,000,000 table rows, the database may inspect a tiny number of index pages plus 120 table rows.

That is the read speedup.

The important detail is that an index does not make the table smaller. It gives the optimizer an alternative access path. If that path is cheaper than scanning the table, the optimizer will use it.

A worked example

Imagine this table:

CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  account_id BIGINT NOT NULL,
  event_type TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  payload JSONB NOT NULL
);

It has 50,000,000 rows.

You run:

SELECT id, created_at, event_type
FROM events
WHERE account_id = 9001
ORDER BY created_at DESC
LIMIT 20;

Assume account 9001 has 40,000 events.

Without a useful index, the database may need to:

1. Scan 50,000,000 rows.
2. Keep only rows where account_id = 9001.
3. Sort those 40,000 matching rows by created_at DESC.
4. Return the first 20.

That is a lot of work to return 20 rows.

Now add:

CREATE INDEX idx_events_account_created
ON events (account_id, created_at DESC);

The index is sorted first by account_id, then by created_at DESC within each account.

Now the database can:

1. Navigate directly to the index region for account_id = 9001.
2. Read entries in created_at DESC order.
3. Stop after 20 entries because of LIMIT 20.
4. Fetch those 20 table rows, unless the index contains everything needed.

This is not merely “an index lookup.” The structure matches the query:

• account_id = 9001 selects a contiguous section of the index.
• created_at DESC is already the order inside that section.
• LIMIT 20 allows the database to stop early.

That is the kind of index that can turn a painful query into a cheap one.

Why writes become more expensive

Indexes speed up reads by adding more organized copies of data. The cost is that every write must keep those copies correct.

Consider an insert:

INSERT INTO orders (id, customer_id, status, created_at, total_cents)
VALUES (123456789, 42, 'paid', now(), 5999);

Without secondary indexes, the database mainly has to:

1. Find space for the new row in a table page.
2. Write the row.
3. Log the change for durability.
4. Update any mandatory structures, such as the primary key index.

With an index on customer_id, it also has to insert an entry into that index:

42 -> location of new row

That sounds small, but mechanically it requires real work:

1. Traverse the index tree to find the correct leaf page for key 42.
2. Latch or lock the relevant index pages while modifying them.
3. Insert the new index entry in sorted position.
4. If the leaf page has no room, split it into two pages.
5. Update parent pages so the tree can find the new split pages.
6. Write the index page changes to the write-ahead log or transaction log.
7. Eventually flush dirty index pages to disk.

A page split is especially expensive. Because index pages have finite space, inserting into the middle of a sorted structure can require making a new page, moving some entries to it, and updating the parent level. That parent page may also split. B-trees are designed to make this bounded and manageable, not free.

Now multiply this by every index on the table.

If orders has these indexes:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Then each inserted row requires entries in all four secondary indexes, plus whatever primary key or clustering structure the database maintains.

An update can be even more subtle.

UPDATE orders
SET status = 'refunded'
WHERE id = 123456789;

If status is indexed, the database cannot simply change the table row. It must also reflect the status change in the index:

1. Remove or invalidate the old index entry for status = 'paid'.
2. Add a new index entry for status = 'refunded'.
3. Log those changes.
4. Maintain any composite indexes containing status.

If the updated column is not indexed, some databases can avoid changing secondary indexes. But not always. Implementation details matter: MVCC systems may create new row versions, clustered storage may move rows, and some optimizations apply only under certain conditions. The safe mental model is: every index that includes a changed value must be maintained, and every additional index increases the write path’s work, logging, locking, cache pressure, and storage.

Deletes have a similar cost. Deleting a row means the corresponding index entries must no longer be visible to future queries. Depending on the database, they may be immediately removed, marked dead, or cleaned up later by background maintenance. Either way, the index now participates in the write.

That is why indexes make writes slower: not because databases dislike indexes, but because indexes are extra persistent data structures that must remain transactionally correct.

Why (a, b) helps with a but not with b

Composite indexes are ordered lexicographically. An index on:

CREATE INDEX idx_example_a_b
ON example (a, b);

is sorted like a phone book sorted by last name, then first name — but let’s be precise.

The entries are ordered by a first. For rows with the same a, entries are ordered by b.

Conceptually:

a | b | row pointer
--+---+------------
1 | 1 | ...
1 | 2 | ...
1 | 9 | ...
2 | 1 | ...
2 | 5 | ...
3 | 1 | ...
3 | 4 | ...

A query filtering on a can use this index well:

SELECT *
FROM example
WHERE a = 2;

All rows with a = 2 are contiguous in the index. The database can navigate to the first (2, anything) entry and scan forward until a changes.

A query filtering on both a and b can use it even better:

SELECT *
FROM example
WHERE a = 2
  AND b = 5;

The database can navigate to the specific (2, 5) region.

A query filtering on a and ranging on b also fits:

SELECT *
FROM example
WHERE a = 2
  AND b >= 10
  AND b < 20;

Because within a = 2, values are sorted by b.

But a query filtering only on b does not generally benefit much:

SELECT *
FROM example
WHERE b = 5;

Rows where b = 5 are not contiguous in an (a, b) index. They are scattered across every value of a:

(1, 5)
(2, 5)
(3, 5)
(4, 5)
...

Since a is the first ordering key, the database cannot jump directly to all b = 5 entries. It would have to scan across many or all a groups looking for b = 5.

Some databases can perform variations such as skip scans under certain conditions, where they repeatedly probe the index for each distinct a. But that only helps when the number of distinct a values is small and the optimizer estimates it as cheaper than a table scan. It is not the general behavior you should count on.

The rule of thumb is the left-prefix rule:

A composite B-tree index is most useful when your query constrains the leading column or columns of the index.

So (a, b) can help with:

WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b BETWEEN ? AND ?

It usually cannot help much with:

WHERE b = ?

For that, you likely need an index beginning with b, such as (b) or (b, a).

When adding an index is the wrong fix

Indexes are powerful, but they are not free, and they do not solve every slow query. Here are three common situations where adding one is the wrong fix.

1. The query returns a large fraction of the table

Suppose:

SELECT *
FROM orders
WHERE status = 'completed';

If 8,000,000 out of 10,000,000 orders are completed, an index on status may not help.

The index can find the completed entries, but then the database still has to fetch 8,000,000 rows. If those row fetches are scattered across the table, the index plan may be worse than simply scanning the table sequentially.

Indexes shine when they eliminate most of the table work. If a predicate is not selective, the database may correctly ignore the index.

A better fix might be:

• Change the query to fetch fewer rows.
• Add a more selective composite index matching additional filters.
• Use partitioning if the access pattern naturally isolates data.
• Precompute aggregates if the query is analytical.

An index on a low-cardinality column is not automatically bad, but it must match a query that actually becomes selective, such as:

WHERE status = 'pending'
  AND created_at >= now() - interval '1 hour'

A composite index on (status, created_at) might be useful there because the combination narrows the result.

2. The query is slow because it does too much work after finding rows

Consider:

SELECT customer_id, SUM(total_cents)
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY customer_id
ORDER BY SUM(total_cents) DESC
LIMIT 100;

An index on created_at may help find recent rows. But if “recent” still means 30,000,000 rows, the expensive part may be grouping and aggregating, not locating rows.

The database has to read many rows, compute sums per customer, sort or rank the groups, and return the top 100. An index does not remove that aggregation work unless it changes the amount of data being aggregated or matches an access pattern that avoids sorting.

The right fix may be:

• Maintain a summary table.
• Use incremental aggregation.
• Limit the time range further.
• Move the analysis to a reporting system.
• Create an index that supports a more selective predicate, not just the visible date filter.

Indexes are access paths. They help the database find rows in an order. They do not make large computations disappear.

3. The predicate is written in a way that prevents useful index access

Suppose you already have:

CREATE INDEX idx_users_email
ON users (email);

But the query is:

SELECT *
FROM users
WHERE lower(email) = lower('ALEX@example.com');

A normal index on email is sorted by the stored email value, not by lower(email). The database generally cannot use that index to directly find the lowercase value. It would have to compute lower(email) for many rows and compare the result.

Adding another ordinary index on email is the wrong fix. The issue is that the predicate does not match the indexed expression.

Better fixes include:

• Store normalized email values.
• Use a case-insensitive column type or collation if your database supports it.
• Create an expression/function-based index:

CREATE INDEX idx_users_lower_email
ON users (lower(email));

The same pattern appears with date conversions:

WHERE DATE(created_at) = '2025-02-01'

A normal index on created_at is usually more useful if the query is written as a range:

WHERE created_at >= '2025-02-01'
  AND created_at <  '2025-02-02'

Now the predicate matches the ordering of the index.

The practical mental model

When deciding whether to add an index, ask four questions.

First: what rows can the database skip?

If the index does not let the database ignore a large amount of data, it may not help.

Second: are the matching index entries contiguous?

For B-tree indexes, the best queries are equality and range conditions that map to a contiguous slice of the sorted index.

Third: does the index order match the query?

For filtering, column order matters. For sorting, direction and leading columns matter. For composite indexes, the left-prefix rule matters.

Fourth: what writes now become more expensive?

Every insert must add index entries. Every delete must remove or invalidate them. Every update to indexed columns must move entries from old key values to new ones. Those changes consume CPU, memory bandwidth, locks or latches, log volume, disk space, and background maintenance.

An index is worth it when the read savings exceed the write and storage costs for your workload. It is wrong when it merely adds another structure the database must maintain without giving the optimizer a cheaper path.

That is the core tradeoff: indexes make reads faster by keeping searchable, sorted copies of selected values; they make writes slower because those copies must be updated every time the data changes.

Take a concrete table. events has 2,000,000 rows averaging about 200 bytes each. With per-row and per-page overhead, roughly 40 rows fit in an 8 KB page, so the table occupies about 50,000 pages, or 400 MB.

Now run:

SELECT * FROM events WHERE tenant_id = 42 AND created_at > '2024-01-01';

With no index, the engine performs a sequential scan: read page 1, deserialise its ~40 rows, evaluate the predicate against each, discard the ones that fail, move to page 2. Repeat 50,000 times. It touches 400 MB and evaluates the predicate 2,000,000 times.

The important property is this: the cost of a scan is proportional to the size of the table, not to the number of rows that match. If 120 rows match, you paid for 2,000,000. If 800,000 match, you paid the same. The scan does have one thing going for it — it is sequential I/O, and it reads each page exactly once — which becomes relevant later.

2. What an index adds, and why that shape

An index is a second, separate data structure containing only the indexed columns plus a pointer back to the row (in PostgreSQL a physical page/offset pair; in InnoDB, the primary key value). Its defining property is that it is kept in sorted order by the indexed columns.

Sorted order is the entire point. Sorted order lets you skip. In an unsorted collection, knowing that page 25,000 doesn't contain tenant_id = 42 tells you nothing about page 25,001. In a sorted collection, if page 25,000 holds tenant IDs 40 through 43, you know with certainty that every page before it holds smaller values and every page after holds larger ones. One page read eliminates millions of rows.

So why not just keep a sorted array? Because inserting into the middle of an array of 2,000,000 entries means shifting everything after it. The B+tree solves that: the sorted entries live in leaf pages, and above them sits a shallow tree of pages containing separator keys that say "values below X are down this branch."

The numbers matter here. An index entry on (tenant_id, created_at) is roughly 24 bytes with overhead, so about 330 entries fit per 8 KB page. Two million entries need ~6,100 leaf pages (about 50 MB). One level up, 6,100 child pointers need ~19 pages. Above that, a single root page. Depth three. That fan-out is why the tree is flat: each level multiplies capacity by ~330, so three levels cover tens of millions of rows and four levels cover billions. Any single-value lookup costs three or four page reads, and the root and upper level are almost always resident in memory.

Leaf pages are also linked to their neighbours, so once you find the first matching entry you can walk forward through matches without returning to the root.

The worked comparison. Suppose tenant 42 has 4,000 rows, of which 120 fall in the date range. With the index:

1. Descend from root to leaf using (42, '2024-01-01') — 3 page reads.
2. Land on the first qualifying entry and walk forward through 120 entries — roughly 1 leaf page.
3. For each of the 120 entries, fetch the heap page holding the actual row — up to 120 page reads, scattered randomly across the table.

Total: about 124 page accesses versus 50,000. Roughly 400× less I/O.

Notice step 3, because it drives everything that follows: the cost of an index lookup is proportional to the number of rows that match, plus a small constant for the descent. Scan cost tracks table size; index cost tracks result size. Every decision about indexing follows from comparing those two.

3. Why writes get more expensive — the mechanism

Without indexes, inserting a row is close to the cheapest operation a database performs: find a page with free space (often just the last one), write the row into it, log it. One page becomes dirty.

Each index turns that into more work:

1. Descend the tree. Three or four page reads to locate the correct leaf. Cached at the top, often not at the bottom.
2. Insert in sorted position. The entry cannot be appended; it must go where the ordering says. That leaf page is now dirty and must eventually be written to disk.
3. Possibly split the page. If the leaf is full, it splits into two half-full pages, and a new separator key is inserted into the parent — which may itself split, potentially all the way to the root. A split is several page writes plus extra WAL, and it leaves both pages half-empty, which is where index bloat comes from.
4. Write-ahead log records for every one of those page modifications, because they all have to be crash-safe.

So a table with five indexes turns one dirty page into six, plus five tree descents, plus five sets of WAL records. That is the write amplification, and it is per-row, not per-statement.

Update and delete are not exempt. An UPDATE that changes an indexed column is a delete-plus-insert in every index whose key includes that column — the entry has to move, because its sort position changed. In PostgreSQL, an update that touches no indexed column and fits on the same page can use the HOT optimisation and skip index maintenance entirely; if it doesn't qualify, MVCC writes a new row version and every index gets a new entry pointing at it. Deletes leave entries behind that vacuum or purge threads clean up later.

Key locality is the part people miss. Inserts into the heap are sequential — they cluster on the last page. Inserts into an index go wherever the sort order puts them. If your key is monotonic (bigserial, ULID, timestamp), every insert lands on the same rightmost leaf page, which stays hot in the buffer pool: cheap, dense, minimal splits. If your key is random (UUIDv4, a hash, an email address), consecutive inserts land on unrelated leaf pages. On a 50 MB index in a machine with plenty of RAM this doesn't matter. On a 20 GB index that doesn't fit in the buffer pool, each insert requires reading a cold page from disk and dirtying it — the same insert workload can be an order of magnitude slower purely because of key ordering.

4. Why (a, b) helps queries on a but not on b

A composite index is sorted by a, and within each group of equal a values, sorted by b. It is exactly ORDER BY a, b. There is no separate sorting by b.

Concretely, for (tenant_id, created_at) the leaf entries read:

(41, 2024-01-03) (41, 2024-06-11) (42, 2023-11-02) (42, 2024-01-04) (42, 2024-01-09) (43, 2023-08-30) ...

WHERE tenant_id = 42 describes a contiguous range of that ordering. Descend once, land on the first entry for tenant 42, walk forward until tenant 43 appears. Adding AND created_at > '2024-01-01' narrows it further, because within tenant 42 the dates are sorted too — you seek straight to (42, '2024-01-01').

WHERE created_at > '2024-01-01' describes no contiguous range at all. Qualifying dates appear inside every tenant's block, scattered across all 6,100 leaf pages. There is no starting point to seek to and no stopping condition. The tree structure is useless; the only option is to examine every entry.

The planner may still do that — a full index scan reads 6,100 pages instead of 50,000 heap pages, and if the index covers all requested columns it can answer without touching the table at all. But that is a linear scan of a smaller object, not a lookup. It is 50× worse than the seek you'd get from an index on created_at.

Two rules fall straight out of this:

• Leftmost prefix. (a, b, c) serves predicates on a, on (a, b), and on (a, b, c). It does not serve b alone or (b, c).
• Only one range column, and it must be last. WHERE a = 1 AND b > 5 seeks precisely. WHERE a > 1 AND b = 5 does not: once a is a range, the entries span many a groups, and b is scattered within them. The index narrows on a only; b is checked entry by entry. Put equality columns first, the range column last.

5. Three situations where an index is the wrong fix

The predicate isn't selective. WHERE status = 'active' on our table, where 400,000 rows (20%) are active. The index finds 400,000 entries, then issues 400,000 heap fetches into a 50,000-page table — it will visit essentially every page, in random order, several times. A sequential scan reads those same 50,000 pages once, in order. The planner knows this and will ignore your index; you'll have paid the write cost for nothing. Rough crossover: above roughly 5–10% of the table, the scan wins. The right fix is usually a partial index on the rare value (WHERE status = 'pending'), which is both selective and small.

The predicate can't use the index. WHERE lower(email) = 'a@b.com', WHERE created_at::date = '2024-05-01', WHERE name LIKE '%smith'. The index is sorted by email, not by lower(email); by timestamp, not by its date cast; and a leading wildcard has no prefix to seek to. The ordering the index provides is not the ordering the predicate needs, so the whole index must be scanned or the table read directly. The fix is an expression index (CREATE INDEX ON users (lower(email))), or rewriting the predicate into a range (created_at >= '2024-05-01' AND created_at < '2024-05-02'), or a trigram/full-text index for the wildcard case.

You already have it. If (tenant_id, created_at) exists and someone adds (tenant_id), the new index answers no query the old one couldn't. It is pure write amplification: another tree descent, another dirty page, another set of WAL records on every insert, and more memory competing for buffer pool space. Before adding an index, check whether an existing one already has your columns as a leftmost prefix — and consider whether extending an existing index is better than creating a new one.

One framing that resolves most remaining cases: an index is a standing cost paid on every write, in exchange for a discount on a particular read. If the read runs once a month and the table takes 5,000 inserts per second, the trade is bad even when the index technically works.

A table's rows live in fixed-size blocks on disk called pages — typically 8 KB in Postgres, 16 KB in MySQL/InnoDB. Rows are packed into these pages more or less in the order they were inserted (this raw, unordered storage is often called the heap). There is no ordering by any column value — row 1 might have customer_id = 88214 and row 2 might have customer_id = 4.

When you run SELECT * FROM orders WHERE customer_id = 4, and there is no index, the database cannot know which pages might contain matching rows, so it reads all of them — a sequential scan. It pulls each page into memory, checks every row on it against the filter, keeps what matches, and discards the rest.

Concretely: a table of 10,000,000 orders, ~200 bytes per row, ~40 rows per 8 KB page, is about 250,000 pages. A full scan means 250,000 page reads, no matter whether the query matches 1 row or 1,000,000 rows. The cost is dictated entirely by table size, not by how selective the filter is. This is why "the query got slow" so often correlates with "the table got big" — you're not paying for the answer, you're paying to look at everything to find the answer.

What structure an index adds, and why that one

An index is a second, separate physical structure, stored in its own pages, containing copies of the indexed column's values plus a pointer back to the row's location in the heap — and critically, those values are kept sorted.

The structure almost every database uses for this is a B-tree (or a close variant). Here's the part that actually matters, mechanically: each node of the tree is itself one page. An internal node holds a sorted list of key values and pointers to child pages; you compare your search value against the keys in the node to decide which single child pointer to follow, then repeat. Because the tree is balanced, every leaf is the same distance from the root, so the number of comparisons — and page reads — needed to find any key is bounded and small.

Why does this beat a scan so badly? Fan-out. An 8 KB page can hold on the order of 500 key+pointer entries (a bigint key plus a pointer is around 16 bytes). With 500-way branching, a tree over 10,000,000 rows is only about log₅₀₀(10,000,000) ≈ 3–4 levels deep. Finding a specific customer_id means reading 3–4 pages to walk root → leaf, then following the pointer(s) from the leaf to the actual row(s) in the heap. That's on the order of 5–10 page reads total, versus 250,000 for a scan. The gap isn't "somewhat faster" — it's a different order of magnitude, and it stays that way as the table grows, because the tree's depth grows logarithmically while the scan's cost grows linearly.

Sorting is the load-bearing element here. It's what lets each node make an exact left/right decision instead of "maybe check this branch too." Without sort order, you couldn't rule out a subtree with a single comparison — you'd have to check it, and the tree degenerates back into something you must scan.

Why writes get more expensive — mechanically, not by assertion

Every index on a table is a separate structure that must remain internally sorted and internally consistent at all times. A write to the table is not one operation — it's one operation per structure.

When you INSERT a row:

1. The row itself is appended to the heap (usually cheap — this is close to sequential I/O).
2. For each index on the table, the database must find the correct sorted position for the new key by traversing the tree (same root-to-leaf walk described above), then insert the key into that leaf page.
3. Insertion into a sorted page means shifting existing entries to keep the page sorted, or, if the page is full, splitting it: allocating a new page, moving half the entries into it, and inserting a new separator key and pointer into the parent. If the parent is also full, it splits too, and this can cascade up the tree.

Two things make this expensive compared to the heap append. First, the location of the insert in an index is determined by the key's value, not by insertion order — so while the heap write is roughly sequential, the index write lands wherever that value sorts, which is effectively random I/O scattered across the index's pages. Second, this entire traversal-plus-possible-split happens once per index. A table with five indexes turns one logical INSERT into one heap write plus five separate tree traversals and potential page splits. UPDATE and DELETE cost the same way — an update to an indexed column is a delete of the old key plus an insert of the new one, in every index that covers that column.

This is the whole story. It's not that "indexes have overhead" as a vague tax — it's that the database is maintaining N additional sorted, page-based structures, and keeping any sorted structure correct after every write requires locating the right spot and possibly reshuffling pages to preserve that order.

Why (a, b) helps a WHERE on a but not one on b

A composite index on (a, b) sorts entries first by a, and within each value of a, by b. The leaf-level order looks like:

(1, 5) (1, 9) (2, 1) (2, 4) (2, 7) (3, 2) (3, 6) ...

A query filtering WHERE a = 2 can use the tree exactly as designed: traverse to the first entry where a = 2, then read a contiguous run of entries until a stops being 2. That run is localized in the tree — a small number of adjacent leaf pages.

A query filtering WHERE b = 4 with no condition on a cannot use this locality at all. Rows with b = 4 are scattered across every a-group — (2, 4) sits far from any other b = 4 row that happens to have a different a. The sort order gives you no way to jump straight to "all the b = 4 rows" because b is only locally sorted within each a. The database would have to inspect essentially every leaf entry to find matches — no better than scanning the table, and often worse, since it also has to chase the index's own pages first. This is the leftmost-prefix rule, and it isn't a database quirk — it's a direct consequence of what "sorted by (a, b)" actually means on disk.

Three situations where adding an index is the wrong fix

Low-cardinality columns. An index on a boolean is_active or a status column with three possible values doesn't narrow anything down — a match on one value could still be 30% of the table, meaning tens of thousands of heap lookups scattered randomly across pages, which is often slower than just scanning sequentially. The optimizer may ignore the index entirely, in which case you've paid the write cost for a structure nothing ever reads.

Non-sargable predicates. WHERE LOWER(email) = 'x' or WHERE created_at::date = '2024-01-01' cannot use a plain index on email or created_at, because the tree is sorted by the raw column value, not the transformed one. The index exists, costs you on every write, and is never consulted, because the thing being searched for isn't the thing that's sorted.

Write-heavy, rarely-queried tables. An audit log or event table that's inserted into constantly but read only occasionally (a monthly report, an admin panel nobody opens) is a case where the index's cost is paid on every single write, continuously, in exchange for a read speedup that's cashed in maybe once a month. The math doesn't favor the index even though, in isolation, it would make that one report query faster.

In all three cases the fix isn't "no index" as a rule — it's recognizing that an index only pays for itself when it actually narrows a search and gets used often enough, relative to how often the table is written, to be worth maintaining a second sorted structure through every one of those writes.

Physically, that means:

• The engine often reads many pages into memory just to inspect them, because rows with a matching customer_id can be scattered anywhere on disk.
• If the table is large, you may end up still paying the cost of reading most of the table, even if only a tiny fraction matches.
• Disk I/O dominates this cost. CPUs are fast; reading from disk is slow. If 10,000 pages must be examined to find 100 matching rows, you’re paying a heavy I/O bill.

Two practical consequences follow:

• Your query latency is driven by how many pages you must touch, not by how many rows you want.
• The fewer random seeks you must perform, the better. Sequential scans can be fast if the table is stored contiguously or if you pull large contiguous chunks, but they still move more data than necessary when only a small slice matters.

In short: without an index, the database treats the table as an unordered heap of rows and relies on scanning to locate the answers. The cost grows roughly with the table size, and the benefit of any single read predicate hinges on how selectively the predicate matches rows.

1. What structure an index adds, and why that structure specifically

An index is an auxiliary data structure designed to answer “which rows satisfy X?” without touching the whole table. The most common form in relational systems is a B-tree (or a close variant). Conceptually:

• The index stores keys that come from one or more columns (the indexed columns) in sorted order.
• Each key is linked to a pointer to the corresponding row (or to the row’s location, such as a row ID or the actual data in a clustered index).
• The index’s internal pages form a tree. You start at the root, navigate down a few levels (logarithmic in the number of keys), and land on leaf pages that contain the actual key-and-pointer pairs.
• The leaf pages are the entry points to the data: you read the index to locate the pointers, then fetch the real rows (unless the index is “covering,” meaning all needed columns are in the index itself).

Why a tree? Because it supports fast navigation. You can binary-search the index for an exact value, or locate a range of values with a contiguous block of leaves. That containment and ordering is what lets the database skip huge swaths of the table when a predicate narrows quickly.

A concrete mental model: think of a phone-book-style index, but instead of names to phone numbers, you have values of a column mapped to pointers to rows. The “alphabetical order” of the index means you can hop to the section for a particular value (or range) with a small, predictable number of disk reads, then follow the pointers to the actual rows.

Note two important details:

• The index is separate from the data table, unless you’re using a clustered index (where the table’s data itself is stored in the index leaf). In most cases, a non-clustered index stores keys with pointers, and the data pages live elsewhere.
• A composite index stores a multi-column key, e.g., (a, b). The keys are ordered first by a, then by b (within equal a’s). That ordering matters for how queries can use the index.

Worked example to anchor the idea (with concrete numbers later in the pieces): suppose you have a million rows, and you create an index on user_id. The index sorts all user_id values and stores a pointer to each row. If you query WHERE user_id = 12345, the database uses the index to locate the exact range of keys matching 12345 (often just a handful of index entries), then reads only the associated rows. If you query WHERE user_id = 12345 AND status = 'open', the index helps locate the candidate rows, and the extra predicate is applied after the pointers are gathered.

1. Precisely why a write becomes more expensive — the mechanism

Writes become more expensive because every index you maintain has to stay consistent with the data. The cost isn’t that “someone told the index to exist”; it’s the mechanical work the database must perform to update the index structure in lockstep with data.

What happens during an insert (and the same logic, mutatis mutandis, for updates and deletes):

• Locate the leaf node where the new key belongs. The system traverses the index from root to leaf, touching one page per level. For a million-entry index, you’ll typically touch O(log N) pages to reach the insertion leaf.
• Insert the key and pointer into that leaf. If there is room, you’re done with a small amount of page I/O.
• If the leaf is full, split the leaf into two pages and promote a key to the parent. That can cascade: the parent may also need to split, and its parent, and so on, up to the root. Each of those steps involves reading and writing several index pages.
• If you’re using a clustered index (the data rows live in the same structure as the index leaf), inserting a new row could require physically placing data in a different page or reordering pages, which may trigger additional data-page splits and more I/O.
• Even when the indexed column isn’t clustered, you still pay the cost of updating the index structure: new keys, possibly new pages, more pages to read and write, and potential consolidation or fragmentation over time.
• There’s also the overhead of maintaining the write-ahead log (WAL) or redo logs, and potentially locking or latching structures to keep the index consistent across concurrent writers and readers.

Concrete intuition you can predict after reading this:

• Each insert, update, or delete on an indexed column touches the data pages (for the row) and touches the index pages along a path from root to leaf.
• Insertion isn’t just “add a row.” It’s “insert a row and update every relevant index key structure,” which sometimes means moving things around inside the index (splits) and possibly moving data around on disk if the index is clustered.
• The deeper the index (the larger N is), the more pages you touch for the index path, and the more pages you might need to rewrite during a split cascade. The work scales with log N for the index traversal, plus the I/O for any page splits and for writing back those pages.

Worked example to crystallize the cost:

• Table: orders with 1,000,000 rows. There is a non-clustered index on (customer_id). Suppose you insert 10,000 new orders in a batch. Each insert must place a new key into the B-tree index. The tree depth might be about 3-4 levels for a million entries. For each insert, you:
• traverse the root → level-1 → level-2 → leaf (4 page reads)
• write the leaf with the new key
• if the leaf splits, write the new sibling and update the parent (potentially multiple page writes)
• the parent split, and possibly a root split, adding a couple of extra pages to read/write
• each new row’s data also gets written to the data table (or to the clustered index leaf, if applicable)

Total I/O per inserted row isn’t fixed, but in a realistic setting it’s tens to hundreds of kilobytes of disk activity per 10,000 rows when you account for both index and data pages, plus the overhead of WAL. Multiply by 10,000 and you’re into significant write amplification, even before considering concurrency control and checkpointing.

The punchline: writes become more expensive because the system does real, observable work to keep the index consistent with the data. The more indexes you maintain, the more paths you must update per write, and the more likely you are to incur splits and rebalancing that ripple through the structure.

1. Why a composite index on (a, b) helps a query filtering on a but not one filtering on b

A composite index on (a, b) sorts keys first by a, and then by b within each equal a. This ordering is what enables certain predicates to take immediate advantage of the index.

• Query 1: WHERE a = 5 The index can jump directly to the portion of the index where a equals 5. All keys with that a value are clustered together in the index, so you quickly locate the set of matching rows (the pointers) without scanning the rest of the table. You then apply any further predicate on b or other columns, but you’ve already narrowed from “the whole table” to “the subset with a = 5.” If the a=5 subset is, say, 1,000 rows out of 1,000,000, you’ve dramatically reduced the amount of data to examine.

• Query 2: WHERE a = 5 AND b = 7 This is even more exact. The index’s ordering supports locating the exact (a,b) pair or the small set of pairs with a=5, b=7. The DB can jump to that range and fetch only the matching rows, making the operation fast.

• Query 3: WHERE b = 7 Here the composite index on (a, b) loses its left-most-prefix advantage. Since the index is ordered primarily by a, the entries with b = 7 are scattered across many a-values, and there is no contiguous run of index entries to rapidly isolate all b=7 matches. The engine might have to scan the entire index (or use a separate index on b if one exists) to find the ones with b = 7. If you don’t also have an index on b (or a different composite that starts with b), the index on (a,b) provides little help for a predicate that filters only on b.

This is the “left-most prefix” principle in practice: the index helps most when your query’s predicates align with the leading columns of the index. If your query filters on b without constraining a, a (a,b) index largely won’t help. If your query asks for a along with b (both), you get excellent benefit.

Concrete visualization:

• Suppose 1,000,000 rows, a has 1,000 distinct values, and b has 10,000 distinct values. The (a,b) index organizes rows by a first. A=5 might correspond to 1,000 rows scattered across b values. A=5 AND b=7 narrows to those 1,000 rows with b=7, perhaps 20 rows. The index helps dramatically here. Filtering on b only would require scanning all a’s in the index to collect the 20 rows with b=7, if there’s no separate b index.

1. Three concrete situations where adding an index is the wrong fix

Situation 1: You’re write-heavy and reads are infrequent or inefficient without the index

• Example: A log table that grows by 500,000 rows per hour, with a handful of fast read queries that run occasionally.
• Why a problem: Every insert touches the index, and the volume of maintenance work grows with the number of indexes. If those read queries are not the hot path, the index adds sustained write overhead without delivering proportional read relief. In such cases, the index is a drag on latency for every write and may not speed up meaningful user-facing queries often enough to justify itself.

Situation 2: The predicate is on a low-selectivity column

• Example: A boolean flag is_active with values true/false, and many or most rows share the same value.
• Why a problem: The index will allow you to locate all rows with a single value, but that’s a large fraction of the table. The resulting I/O and row fetch cost may approach or exceed what a table scan would cost, and you’ve paid for maintaining the index for every write. A partial index, a filtered index (e.g., only where is_active = true), or simply scanning the table may be a better fit depending on the workload.

Situation 3: The composite index is misaligned with common queries

• Example: You create an index on (start_date, end_date) but most queries filter on end_date only, or on a date range with vague bounds, or you frequently search by end_date while ignoring start_date.
• Why a problem: The index can’t help as much as you expect because the leading column (start_date) is not being constrained in your most common queries. You’re paying index maintenance cost for limited benefit. The right fix often isn’t to drop the index entirely but to adjust your indexing strategy: add a separate index on end_date, or rewrite queries to leverage the leading column, or use a partial/filtered index that matches your actual search patterns.

Worked example tying the parts together

Scenario: A orders table, 1,000,000 rows. We want to answer: “Find all orders placed by a specific customer_id within the last 90 days, and return order_id, total_amount, and status.” There are two indexing options:

• Index A: a non-clustered index on (customer_id, order_date)
• Index B: a non-clustered index on (order_date) plus a separate index on (customer_id)

Query: SELECT order_id, total_amount, status FROM orders WHERE customer_id = 12345 AND order_date >= '2026-05-01';

Assume uniform distribution: 1,000,000 rows, 100,000 with customer_id = 12345; of those, 10,000 fall in the last 90 days.

With Index A (customer_id, order_date):

• The engine uses the index to locate the range where customer_id = 12345 (logarithmic search down the B-tree).
• It then applies the date predicate on the candidate set, returning about 10,000 rows.
• It fetches those 10,000 rows. If the index is covering the needed columns (or if data is cached), this can be fast; otherwise, it performs 10,000 row fetches from the data pages.
• Benefit: avoids scanning the entire 1,000,000-row table; you read only the subset for that customer and date range.

With Index B (order_date) plus separate index on customer_id:

• The date predicate would first locate a date range (potentially large, depending on the distribution of orders across dates). If 90 days cover, say, 100,000 rows in that date window, you still must intersect that with 12345’s orders, which is expensive unless you can efficiently perform an index intersection.
• If the database can’t cheaply intersect the two indexes, you may end up scanning the date index and then verifying the customer_id for each candidate row, or you might revert to a slower plan.
• The hit rate depends on distribution and the engine's planner, but this is generally less predictable than a well-structured composite index for this exact query.

Takeaway from the example: when your predicate combines two fields that share a leading column in a composite index, that index can dramatically prune the search space, often in a single linear pass through the index. A poorly aligned or duplicate indexing strategy can yield far less benefit, or even worse, add overhead without meaningful reads improvement.

Putting it all together

• Indexes speed reads by converting a linear scan into a logarithmic search through a tree structure, then a targeted fetch of the matching rows.
• The same structure makes writes slower because every insert, update, or delete must also modify the index to stay consistent, which may involve splits, rebalancing, and page I/O.
• The practical effectiveness of an index depends on selectivity (how many rows are matched), data distribution, and whether the query patterns align with the index’s leading columns (the left-most-prefix rule).
• Composite indexes are powerful when queries filter on the leading column; they offer strong support for queries that specify the leading column, and optional benefits for multi-predicate filters on that same leading column. If queries use the trailing column(s) without the leading one, the composite index often provides little help.
• Three concrete reasons not to add an index: (a) write-heavy workloads where reads don’t justify the maintenance cost; (b) low-selectivity predicates where the index doesn’t prune much; (c) misaligned query patterns where the index’s leading column isn’t used in practice.

If you’re deciding whether to add or remove an index, quantify the plan you expect for your most common queries. Roughly estimate:

• How many rows the predicate matches (selectivity).
• How many pages you’d need to read with a full scan vs. with an index look-up.
• How often the data changes (inserts/updates/deletes) for that column and the expected index maintenance cost.

Then compare the read latency improvements to the write overhead and the overall impact on latency under peak load. The mental model that emerges should be simple: an index is a faster map to the data, but every write must redraw that map as well.

If you want, I can tailor a worked example to your actual table schema and a couple of real queries from your codebase, so you can predict the read/write tradeoffs with your data distribution.

The Database Without an Index: A Full Table Scan

Imagine you have a users table with 10 million rows. Each row stores details about a user, let's say about 1 kilobyte of data. The total size of this table is roughly 10 gigabytes.

Now, consider a query like this:

SELECT first_name, last_name, email
FROM users
WHERE email = 'alice@example.com';

Without an index on the email column, your database has only one way to find Alice: it has to read every single row in the users table, one by one, from beginning to end, checking the email column of each row until it finds 'alice@example.com' (or concludes she doesn't exist).

This process is called a "full table scan."

Why this is slow (in physical terms):

Database tables are stored on disk. Reading data from disk is orders of magnitude slower than reading data from your computer's RAM or CPU cache. When performing a full table scan, the database's primary bottleneck is disk I/O. It needs to fetch all 10 GB of the users table data from disk into memory, process each row, and then discard most of it, just to find one or a few matching rows. Even with modern SSDs, reading 10GB sequentially takes a noticeable amount of time. For spinning HDDs, it would be excruciatingly slow.

The database can't jump directly to Alice's row because there's no ordered structure telling it where to go. It’s like looking for a specific sentence in a book that has no table of contents, no page numbers, and is just a continuous stream of text – you have to read the whole thing until you find what you're looking for.

The Structure an Index Adds: The Sorted Tree

An index solves the full table scan problem by creating a separate, highly organized data structure. Think of it like the index at the back of a textbook: it lists keywords and the page numbers where they can be found. In a database, an index lists indexed column values and the physical location (or primary key) of the corresponding rows in the main table.

The key to an index's speed lies in two properties: sorting and a tree-like structure.

1. Sorting: When you create an index on a column (e.g., email), the database builds a new structure where all the email values are stored in sorted order. If you're looking for 'alice@example.com', a sorted list allows for very fast searching – essentially a binary search. You can quickly eliminate large chunks of the data, dramatically reducing the amount of information you need to examine.

1. Tree-like Structure: While a simple sorted list is efficient for searching, it can become very large for tables with millions of rows. To make searching even faster and to minimize disk I/O, databases typically organize this sorted list into a tree structure (most commonly a B-tree or a variation).

• Nodes: The tree is made up of "nodes." Each node is a block of data, typically sized to match a disk page (e.g., 8KB or 16KB).
• Pointers: Each node contains a range of key values and pointers. These pointers either point to other nodes lower down in the tree (internal nodes) or to the actual data rows in the main table (leaf nodes).
• How it works: To find 'alice@example.com' using an index:

1. The database starts at the "root" node of the tree. This node contains ranges of email addresses and pointers to other nodes.
2. It quickly determines which child node is likely to contain 'alice@example.com' based on the ranges in the root node.
3. It follows that pointer to the next node (an internal node) and repeats the process.
4. This continues until it reaches a "leaf" node. The leaf nodes contain the actual indexed email values in sorted order, along with pointers to the specific rows in the main users table.
5. The database then uses this pointer to fetch Alice's full row directly from the main table.

Why this structure is fast:

Instead of reading 10GB of raw table data, the database might only need to read 3-5 index nodes (each being a small disk page, e.g., 8KB) to find the pointer to Alice's row. That's a tiny fraction of the original I/O – perhaps a total of 40KB instead of 10GB. Once it has the pointer, it reads just that single 1KB row (or disk page containing it) from the main table. This minimal disk access is the core reason reads are so much faster with an index.

Precisely Why a Write Becomes More Expensive

The speed of reads comes directly from the index's highly organized and sorted structure. However, this structure isn't static; it must be maintained every time the underlying table changes. This maintenance is the mechanism that makes writes slower.

For every index on a table, any write operation (INSERT, UPDATE, DELETE) that affects an indexed column will incur an additional cost to update the index.

1. INSERT Operations:

• When you insert a new row into the users table, the database first writes the new row to the main table.
• Then, for every index on that table, the database must create a corresponding new entry in the index.
• Since the index must maintain its sorted, tree-like structure, the database needs to find the correct sorted position for this new entry within the index. This isn't just appending to the end.
• Inserting into the middle of a sorted structure requires shifting existing entries to make space. If an index node becomes full, it might need to "split" into two new nodes, and this split might propagate up the tree, requiring updates to parent nodes. This process involves additional disk I/O and CPU work to reorganize the index structure, ensuring it remains balanced and efficient for future reads.

1. UPDATE Operations:

• If you update a column that is part of an index (e.g., changing Alice's email address):
• The database essentially treats this as a "delete" of the old value from the index, followed by an "insert" of the new value into the index.
• The old entry for 'alice@example.com' must be located and removed (or marked for deletion) from the index.
• The new entry for 'new.alice@example.com' must be inserted into its new, correct sorted position within the index.
• Both these operations carry the overhead described for inserts and deletes (finding the spot, shifting entries, potential node splits/merges).
• If you update a column that is not part of an index (e.g., changing Alice's first_name):
• The index on email is completely unaffected. Only the main table row needs to be updated.

1. DELETE Operations:

• When you delete a row from the users table, the database removes the row from the main table.
• For every index, the corresponding entry must also be removed.
• Locating the entry within the index tree and removing it can also involve reorganization. If a node becomes too empty after a deletion, it might "merge" with an adjacent node, again requiring updates to parent nodes and additional disk I/O.

In summary: Each index on a table is a separate, persistent data structure that needs to be kept perfectly in sync and optimally organized whenever its underlying data changes. This constant maintenance, involving disk I/O for reading and writing index pages, and CPU cycles for reordering and balancing the tree, is the direct mechanism that makes write operations slower. The more indexes a table has, the higher the cumulative overhead for write operations.

Why a Composite Index on (A, B) Helps A But Not B

Sometimes, a query needs to filter on multiple columns. For this, you might create a "composite index" that includes more than one column, such as (customer_id, order_date). This type of index orders the data first by customer_id, and then, for all rows that have the same customer_id, it further orders them by order_date.

Think of it like a phone book sorted by (Last Name, First Name).

Let's consider a orders table with 100 million rows and a composite index on (customer_id, order_date).

1. Query filtering on customer_id (the leftmost column):

    SELECT * FROM orders WHERE customer_id = 123;

This query can efficiently use the index. Just like searching by last name in our phone book, the database can quickly navigate the index tree using customer_id to find all entries for customer_id = 123. The index is perfectly sorted for customer_id, allowing for rapid lookup.

1. Query filtering on customer_id AND order_date:

    SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

This query also benefits greatly. First, the database uses the customer_id part of the index to find all orders for customer 123. Then, within that already sorted subset, it can efficiently use the order_date part of the index to find orders placed after '2023-01-01'. The index allows it to quickly jump to the relevant range of order_date values for that customer.

1. Query filtering ONLY on order_date (not the leftmost column):

    SELECT * FROM orders WHERE order_date = '2023-10-26';

This query cannot efficiently use the composite index for direct lookup. Why? Because while order_date is sorted within each customer_id group, it is not sorted globally across the entire index.

• In our phone book analogy, if you want to find everyone named "John," you can't just jump to a "J" section, because "John" appears under "Johnson," "Doe," "Smith," etc. You'd have to read through a significant portion of the phone book.
• Similarly, the database cannot use the tree structure of the (customer_id, order_date) index to quickly locate specific order_date values without knowing the customer_id. It would have to scan a large part of the index to find all matches, effectively losing the benefit of the sorted tree structure for order_date alone. This is known as the "left-most prefix" rule: a composite index can only be used effectively if the query uses the leftmost columns of the index, or a prefix of them.

Three Concrete Situations Where Adding an Index Is the Wrong Fix

While indexes are powerful, they are not a silver bullet. Understanding their costs helps identify situations where they might do more harm than good.

1. Tables with Very High Write Throughput:

• Mechanism: As detailed earlier, every insert, update, or delete operation on an indexed column requires corresponding updates to all relevant indexes. In tables designed for extremely high write volumes (e.g., logging systems, IoT data streams, real-time analytics event tables), this overhead quickly becomes significant. Each write operation effectively turns into multiple write operations (one for the table, one for each index), multiplying disk I/O, CPU usage, and potentially increasing lock contention.
• Example: A sensor_readings table receiving 10,000 new rows per second. Adding an index to timestamp to speed up a rare analytical query might introduce so much overhead that the entire ingestion pipeline slows down, backlog queues grow, and the system struggles to keep up with the incoming data. Prioritizing write speed often means minimizing indexes.

1. Columns with Very Low Cardinality:

• Mechanism: An index is most effective when it significantly narrows down the search space. If a column has very few distinct values (low cardinality), an index on that column won't filter out many rows. For example, a status column might only have 'active', 'inactive', 'pending', 'archived'. If you query WHERE status = 'active' and half your 10-million-row table is 'active', the index might find 5 million pointers. Traversing the index and then fetching 5 million individual rows from the main table might be slower than simply performing a full table scan, especially if the table data is relatively small or already cached in memory. The database's query optimizer often makes this exact decision, ignoring an index on a low-cardinality column.
• Example: A user_profiles table with 50 million rows and an is_admin boolean column. Indexing is_admin to find WHERE is_admin = TRUE might not be beneficial if only 1% of users are admins. If only 1% of users are admins, the index lookup is fast, but then fetching 500,000 user rows from the table could still be slow. If 50% are admins, the index will likely be ignored.

1. Columns Frequently Updated (that are part of an index):

• Mechanism: If a column that is part of an index is updated very frequently, the database incurs the cost of index maintenance (effectively a delete + insert within the index) on almost every update. This constant churn can lead to fragmentation within the index structure, meaning index pages become less efficiently packed, requiring more disk I/O to traverse them. It also ties up resources (CPU and I/O) that could be used for other operations. The benefits of faster reads might be entirely negated by the cost of maintaining the index for these high-frequency updates.
• Example: A user_sessions table where a last_activity_timestamp column is updated every few seconds for millions of active users. An index on last_activity_timestamp to find recently active users would be subject to constant, massive updates. Each update would trigger index maintenance, leading to significant write contention and potentially degrading overall database performance, far outweighing any gains from faster reads on that specific column.

Conclusion

Database indexes are a fundamental tool for optimizing read performance, transforming slow, full table scans into lightning-fast lookups. Their power comes from creating a separate, sorted, tree-like data structure that dramatically reduces the amount of disk I/O required to find specific data.

However, this efficiency comes at a direct cost: every write operation that touches an indexed column incurs additional overhead to maintain these precisely organized structures. Understanding this mechanism – the finding, shifting, splitting, and merging of index nodes – is key to appreciating why writes slow down. Furthermore, knowing the "left-most prefix" rule for composite indexes and recognizing situations where indexes are counterproductive empowers you to make informed, rather than reactive, decisions about your database schema.

Before adding an index, consider your workload: are reads or writes more dominant? What is the cardinality of the column? How frequently is it updated? Using tools like EXPLAIN (or your database's equivalent) to analyze query plans will help you see the database's actual execution strategy and confirm whether an index is being used as intended, guiding you toward truly optimized performance.