Understanding SQL Indexes and Query Performance

Part 13 of 16 · ~3 min

The Write-Performance Cost of Indexes

Time to pay the bill promised in chapter 1.

Here is everything we have added to Employees:

CREATE INDEX idx_emp_dept             ON Employees (dept);                 -- chapter 1
CREATE INDEX idx_emp_dept_covering    ON Employees (dept, name, salary);   -- chapter 4
CREATE INDEX idx_emp_dept_salary_name ON Employees (dept, salary, name);   -- chapter 5
CREATE INDEX idx_emp_salary           ON Employees (salary);               -- chapter 6
CREATE INDEX idx_emp_name_lower       ON Employees (LOWER(name));          -- chapter 6

Five B-trees, each a separate sorted structure over 2.4 million rows, and here is the part that is easy to miss: every one of them has to be kept correct on every write. Not lazily, not on a schedule — as part of the same transaction, before the write is acknowledged.

The UPDATE row is the expensive one, and the reason is structural. Changing an indexed value is never an edit in place. The entry's position in the tree is determined by the value, so changing the value means the entry belongs somewhere else — delete from here, insert over there, potentially splitting a page at the destination and leaving a gap at the origin.

Now the raise run, which is the write this database cares about most. Once a year, every compensation decision from the review lands in one statement per department:

UPDATE Employees
SET salary = CAST(salary * 1.04 AS INTEGER)
WHERE dept = 'Sales';

Look at what that does to our five indexes. It changes salary on 431,000 rows. salary appears in three of them — idx_emp_dept_covering, idx_emp_dept_salary_name, and idx_emp_salary. So each of those 431,000 row updates is one table write plus three delete-and-reinsert pairs, and because every salary moved, essentially every affected entry lands in a new position. Count it: about 1.3 million index entries relocated, each relocation being a delete plus an insert, so roughly 2.6 million individual B-tree operations — to express 431,000 logical changes.

This is the trade, stated as concretely as it can be stated: the indexes that took the pay-band screen from four seconds to four milliseconds are the same indexes that made the raise run several times slower, and they did it because both operations are about the same column. It is not a hidden cost or a gotcha. It is the deal, and here it is a good deal — the screen runs thousands of times during review week, the raise run happens once a year, and it can run overnight.

Which is exactly how to think about every index: not "is this index worth it" in the abstract, but "what does this specific workload read, what does it write, and how often does each happen." Two indexes in that list do not survive that test, and the next chapter removes them.

The other reason indexing every column "just in case" fails is that the cost is certain while the benefit is speculative. An unused index is pure overhead — storage, write amplification, one more structure in the optimizer's search space — and returns nothing, because no query ever asks for it. A table's indexes should be a reviewed set with a reason each, not an accumulation.