Part 2 — Querying › Chapter 07

Predicates, filtering and sargability

WHERE, LIKE, IN, BETWEEN — and the one habit that quietly disables every index you own.

From the advert

“Ottimizzazione e tuning delle query” — most “tuning” turns out to be one of the four habits in this chapter, found and removed.

The word they will use: sargable

Why wrapping the column in a function turns a seek into a scan The index is sorted by the stored column value. A comparison against the bare column names a contiguous stretch of it, so the engine jumps there and reads only that stretch. Wrapping the column in a function compares something the index does not store, so every entry has to be read and tested. SARGABLE placed_at >= '2026-01-01' AND < '2027-01-01' index, sorted by placed_at → read one contiguous stretch: a few page reads, whatever the table size NOT SARGABLE strftime('%Y', placed_at) = '2026' every entry read and tested the index stores placed_at, not strftime(placed_at) — so it cannot be searched
Both return the same rows. Only one of them can use the index, and nothing in the SQL looks wrong.
🧒 Simple

A phone book is sorted by surname, so finding “Rossi” is instant. But if I ask for everyone whose surname has six letters, the sorting is useless — you have to read every page and count. The information was there; the way I asked destroyed the shortcut.

🎓 Professional

A predicate is sargable (Search ARGument able) if the engine can use it to seek into an index instead of scanning. The rule is short: the indexed column must appear alone on one side of the comparison, untouched. Wrap it in a function, do arithmetic on it, or force a type conversion, and the index on it becomes unusable — because the index stores column, not f(column), and the engine has no way to know what range of stored values satisfies your expression.

The four ways people destroy an index

1 · A function on the column

Way one to destroy an index: wrap the column in a function.
-- NOT SARGABLE: the index stores placed_at, not YEAR(placed_at).
WHERE YEAR(placed_at) = 2026          -- the engine must compute YEAR() for every row first

-- SARGABLE: an ordinary range over the values that are actually stored.
WHERE placed_at >= DATE '2026-01-01'  -- lower bound, inclusive — an index seek starts here
  AND placed_at <  DATE '2027-01-01'  -- upper bound, EXCLUSIVE — and the seek stops here
Why the second version can use the index
  • An index is a sorted copy of placed_at. Sorted by placed_at — not by YEAR(placed_at), which is a different value the index has never seen. The engine cannot work out which stored values satisfy YEAR(x) = 2026 without computing it for each of them, and computing it for each of them is a scan.
  • < and not <= on the upper bound: a half-open range. It is correct for every precision the column could ever have — dates, milliseconds, nanoseconds — so you never have to think about '2026-12-31 23:59:59.997' again.
  • This is also why BETWEEN is a trap on timestamps: it is inclusive at both ends, so it silently drops everything after midnight on the last day.
Why half-open, not BETWEEN

BETWEEN '2026-01-01' AND '2026-12-31' silently drops everything that happened on 31 December after midnight, because a timestamp of 23:59:30 is greater than the date 2026-12-31 at 00:00. The >= start AND < next-start form is correct for dates, timestamps and every precision the column might later be changed to. Use it always and you never have to think about it again.

2 · Arithmetic on the column

Way two: arithmetic on the column instead of on the constant.
WHERE total_cents / 100 > 500      -- NOT sargable: a division per row, then a comparison
WHERE total_cents > 50000          -- sargable: the same question, maths moved to the constant
The conversion hiding in the first line
  • total_cents / 100 is INTEGER ÷ INTEGER, so it truncates: 50099 / 100 is 500, not 500.99. The first line therefore excludes an order of €500.99 that the second line includes. It is not only slower — it is a different query.
  • The rule generalises: keep the column bare on one side of the comparison and do the arithmetic on the literal. The optimiser will not rearrange the algebra for you.

3 · A leading wildcard

Way three: a leading wildcard, which asks the index a question it cannot answer.
WHERE name LIKE '%rossi%'          -- scan: an index is sorted by prefix, and there is no prefix
WHERE name LIKE 'rossi%'           -- seek: this IS a prefix, so it is a range over the index
What the index can and cannot do here
  • Think of a phone book. “Surnames starting rossi” is a place you can open it. “Surnames containing rossi” means reading every page — and no amount of indexing changes that, because the ordering is by first letter.
  • If you genuinely need infix search, the answer is not an index on this column: it is full-text search, a trigram index, or a search engine (chapter 43). Saying that in an interview is worth more than saying “add an index”.
  • Both forms are also collation-sensitive: whether 'ROSSI' matches depends on the column's collation, not on the pattern.

If you genuinely need infix search, an index cannot help and you need a different tool: a full-text index, a trigram index (pg_trgm), or a search engine (chapter 43). “Add an index” is the wrong answer and saying so is the right one.

4 · Implicit conversion — the invisible one

Way four, the invisible one: a type mismatch the engine fixes by converting your column instead of your literal.
-- vat_number is VARCHAR. The literal below is a NUMBER.
WHERE vat_number = 12345678901     -- the engine converts the COLUMN, on every row, to compare
-- Plan: a scan, plus a CONVERT_IMPLICIT warning that nobody ever reads.

WHERE vat_number = '12345678901'   -- sargable: text compared to text, no conversion, index seek
The rule that decides which side gets converted
  • When two types meet, SQL converts the one with lower precedence — and numeric types outrank string types in every major engine. So text-versus-number converts the text, and the text is your indexed column, once per row.
  • The same trap appears between VARCHAR and NVARCHAR in SQL Server: the VARCHAR column is converted to Unicode and the seek disappears. Match the parameter's type to the column's type, in the application as well as in the SQL.
  • Nothing fails. The result is correct and the query is a hundred times slower, which is why this one survives code review.
⚠ Trap

Implicit conversion is the hardest of the four to spot because the query is correct and returns the right answer — it is merely a hundred times slower. It appears when an ORM binds a parameter as NVARCHAR against a VARCHAR column (a classic .NET/SQL Server incident), or when a column that holds digits was declared as text. In SQL Server the plan carries a CardinalityEstimate or PlanAffectingConvert warning; in PostgreSQL you will see a cast in the filter. Look for it whenever a query is slow and the index “obviously” exists.

OR, IN and the shape of the range

IN (a, b, c) is a union of equality seeks and is generally fine. OR across different columns is not: no single index covers both sides, so the engine often falls back to a scan.

OR across two different columns, and the rewrite that gives the optimiser two seeks instead of one scan.
-- Frequently a scan: no single index has BOTH columns as its leading key.
WHERE email = ? OR phone = ?       -- ? is a bound parameter: the driver sends the value separately

-- Two seeks the optimiser can cost independently, then combine:
SELECT ... WHERE email = ?         -- uses the index on email
UNION                              -- stacks the two results and removes duplicates
SELECT ... WHERE phone = ?         -- uses the index on phone
Two things worth taking from this
  • OR between values of the same column is fine — the engine turns it into a list of seeks. OR across different columns is the problem, because one index cannot lead with both.
  • UNION deduplicates, which is what you want when a person could match on both columns. UNION ALL would return them twice, and is cheaper when they cannot overlap.
  • The ? placeholders are parameters, not string formatting. That is what makes the plan reusable — and what makes injection impossible (chapter 34).
🎓 The optional-parameter pattern, and why it is poison

This shape appears in every reporting screen ever built, and it is the single most common cause of a catastrophically bad cached plan:

The catch-all search predicate: one query, every optional filter, and one cached plan that fits none of them.
WHERE (@customer_id IS NULL OR customer_id = @customer_id)  -- "if the caller supplied one"
  AND (@status      IS NULL OR status      = @status)       -- …and again, per filter
Why this shape is poison
  • @customer_id is a parameter. The plan is compiled once, for whatever values arrived first, and then reused for every other combination — so the plan built for “customer supplied, status empty” is served to “both empty”, which needed a completely different one.
  • The optimiser cannot fold @x IS NULL OR col = @x into a seek, because at compile time it does not know which branch will be true. It plans for both, and gets a scan.
  • The fixes, in order of preference: OPTION (RECOMPILE) — a fresh plan per execution, usually cheap next to the scan it removes; or genuinely dynamic SQL built with sp_executesql and parameters (chapter 34), never string concatenation.

One plan is compiled and reused for every combination of supplied and omitted parameters, so a plan built for “no filters at all” gets reused when a single customer is requested, and vice versa. The fixes are OPTION (RECOMPILE), or building the statement with safe dynamic SQL and parameters (chapter 34). Naming this pattern in an interview is a strong signal — it means you have maintained a reporting screen.

See it for yourself: two ways to ask for January, and two different plans.
-- Run both in the playground and compare. (EXPLAIN QUERY PLAN in SQLite,
-- EXPLAIN ANALYZE in PostgreSQL, "Include Actual Execution Plan" in SSMS.)
EXPLAIN QUERY PLAN                                      -- ask for the plan, do not run the query
SELECT * FROM orders                                    -- the same rows either way…
WHERE placed_at >= '2026-01-01' AND placed_at < '2026-02-01';  -- …asked for as a RANGE

EXPLAIN QUERY PLAN                                      -- now the other spelling
SELECT * FROM orders                                    -- same table, same answer
WHERE strftime('%Y-%m', placed_at) = '2026-01';         -- a FUNCTION on the column: no seek
How to read what comes back
  • SEARCH orders USING INDEX … means the engine jumped straight to the rows. SCAN orders means it read all of them. That one word is the difference this chapter exists to teach.
  • strftime('%Y-%m', placed_at) returns TEXT, so the comparison is a string comparison against a computed value. It is correct, it is readable, and it cannot use the index — the two ideas are independent.
  • The range version compares TEXT to TEXT too, in SQLite, because SQLite stores these timestamps as ISO-8601 strings. It still seeks, because ISO-8601 sorts chronologically as text: the format was designed for exactly this.

When you cannot change the query

Sometimes the non-sargable expression is in a vendor application you cannot edit. Two ways out, both worth knowing by name:

  1. Sargable means the engine can seek. The indexed column must appear alone and untouched on one side of the comparison.
  2. Never wrap the column in a function. Rewrite as a half-open range: >= start AND < next_start.
  3. BETWEEN on a timestamp loses the last day. Half-open ranges do not.
  4. Move arithmetic to the constant side of the comparison.
  5. LIKE 'x%' seeks; LIKE '%x%' cannot. Infix search needs full-text or trigrams, not an index.
  6. Implicit conversion silently disables an index and returns the right answer, so nothing looks broken. Check the plan for a convert warning.
  7. OR across different columns often defeats indexing; UNION of two seeks lets the optimiser cost each half.
  8. The (@p IS NULL OR col = @p) pattern caches one plan for every parameter combination. Recompile it or build it dynamically with parameters.
  9. If you cannot change the query, index the expression — an expression index or a persisted computed column.

Interview questions this chapter answers

What does “sargable” mean?

That a predicate can be used to seek into an index rather than forcing a scan. It requires the indexed column to appear alone on one side of the operator — no function, no arithmetic, no implicit conversion. The index stores the column's values, so an expression over it cannot be turned into a range.

This query has an index on the date column and still scans. Why?

Almost certainly the predicate wraps it: YEAR(placed_at) = 2026, CAST(placed_at AS DATE) = …, or a type mismatch causing an implicit convert. Rewrite it as a half-open range on the raw column and the seek comes back.

How would you make LIKE '%text%' fast?

You cannot, with a B-tree — there is no prefix to seek on. The honest answer is to change the tool: a full-text index, a trigram GIN index in PostgreSQL, or an external search engine if the requirement is really relevance-ranked search rather than substring matching.