Understanding SQL Indexes and Query Performance

Part 7 of 16 · ~5 min

SARGability: Why Wrapping a Column in a Function Breaks Index Usage

The pay-band screen is fast now. This chapter is about the single easiest way to make it slow again, without touching a single index.

A predicate earns the label SARGable — short for "Search ARGument-able" — precisely when an index can be handed the condition directly and used for a seek. Flip that around and a predicate is non-SARGable when the engine must first compute something per row before it can even test the condition — which forces it to touch every row, which is just a scan by another name.

The rule that decides which is short: an index is sorted on the raw values in the column. If your WHERE clause does not compare the raw column, the index's ordering does not apply to what you are asking.

Here is the pay-band screen acquiring a new feature. HR wants to filter by salary band rather than by an exact range — "show me everyone in the 60s." The direct translation of that sentence into SQL is a division:

-- Salary band 6 = everyone from 60000 to 69999. Integer division, so this is correct.
SELECT name, salary FROM Employees WHERE salary / 10000 = 6;

It returns exactly the right people. Give it the best possible chance — an index on precisely the column it filters on:

CREATE INDEX idx_emp_salary ON Employees (salary);

Its plan is:

SCAN Employees

A full scan, with the index sitting right there unused. And the index is right to be unused. It is sorted on salary — 50000, 55000, 60000, 70000 — and the query is not asking about salary. It is asking about salary / 10000, a value that appears nowhere in the index, in no order, for any row. To find the rows where that expression equals 6, the engine has no choice but to compute it 2.4 million times.

Rewrite the same question as a range on the raw column:

SELECT name, salary FROM Employees WHERE salary >= 60000 AND salary < 70000;

SEARCH Employees USING INDEX idx_emp_salary (salary>? AND salary<?)

Identical rows, and now it is a seek. The half-open interval — >= on the lower bound, < on the upper — is the general form of this rewrite and worth internalising as a shape: it is how you turn any bucketing-by-function into a range, and unlike BETWEEN it composes correctly with the next band without overlapping or leaving a gap.

Arithmetic falls into that identical trap, but this time there's a sting the obvious fix doesn't remove. Say managers may award a 10% raise if it keeps the person under 66000:

-- NOT SARGable — salary * 1.1 has to be computed for all 2.4 million rows first
SELECT name FROM Employees WHERE salary * 1.1 > 66000;

The standard advice is to move the arithmetic to the other side, so the column stays raw. It is correct advice about SARGability, and applied carelessly it is also a bug:

-- SARGable, and NOT equivalent to the query above
SELECT name FROM Employees WHERE salary > 66000 / 1.1;

Run both against the code lab's five rows. The first returns Carol. The second returns Bob and Carol. Bob earns exactly 60000, which is exactly the boundary, and the two queries disagree about him — because 66000 / 1.1 in IEEE 754 double precision is not 60000. It is 59999.99999999999, and 60000 is greater than that. Meanwhile 60000 * 1.1 comes out as exactly 66000.0, which is not greater than 66000, so the original excludes him.

That is not a rounding curiosity you can wave off; on a salary table it is one employee's compensation review going the wrong way, and it will happen to whoever sits precisely on a band edge. When you move arithmetic from the column to the constant, compute the constant exactly and write it as a literal rather than leaving a division in the query for the engine to evaluate in binary floating point:

-- SARGable and exact — the boundary was worked out once, by a human, and written down
SELECT name FROM Employees WHERE salary > 60000;

String functions break SARGability the same way, and the directory's name search is where you meet it:

-- NOT SARGable — an index on `name` is sorted on 'Alice', not on 'alice'
SELECT * FROM Employees WHERE LOWER(name) = 'alice';

LIKE deserves its own note, because the two halves behave completely differently. A trailing wildcard (name LIKE 'Al%') is logically a range — every string that starts with Al sits in one contiguous block of a sorted index, between 'Al' and the next prefix up — so engines can convert it into a seek. A leading wildcard (name LIKE '%ce') cannot be, ever, by any engine: the strings ending in ce are scattered throughout the sort order, and no amount of B-tree cleverness groups them.

Even the trailing case comes with conditions, which is a good demonstration that "is this SARGable" is an engine question and not only a SQL one. In Postgres, LIKE 'Al%' uses a normal B-tree index only under the C locale; under any other collation you need an index built with text_pattern_ops. In SQLite, LIKE is case-insensitive for ASCII by default while a TEXT column's default collation is BINARY, so the two do not match and the optimization is skipped — which is why, with an index on name sitting right there, WHERE name LIKE 'A%' still plans as a bare SCAN Employees. Getting it to seek means aligning the two, by declaring the column COLLATE NOCASE or by turning on case_sensitive_like.

When looking up rows by some transformed version of a column is a routine, recurring need rather than a one-off, the real escape hatch is a functional index (also called an expression index — Postgres, SQLite, and recent MySQL all support some form):

-- Index the result of the expression, so the expression becomes seekable
CREATE INDEX idx_emp_name_lower ON Employees (LOWER(name));

SELECT * FROM Employees WHERE LOWER(name) = 'alice';  -- SARGable again

The catch is that the match has to be exact. That index accelerates LOWER(name) and nothing else — it does nothing for UPPER(name), nothing for LOWER(TRIM(name)), and nothing for a plain name = 'Alice'. It is a structure that exists to serve one expression, and it is maintained on every single write like any other index. Deliberate trade-off, not a reflex.