A predicate is "SARGable" (Search ARGument-able) when the database can use an index to evaluate it directly, without having to compute something for every row first. Wrapping the indexed column in a function usually breaks this, because the index is sorted on the raw column values, not on the result of some function applied to them:
-- NOT SARGable — the optimizer must evaluate YEAR(order_date) for every row,
-- so it can't seek using an index on order_date; it has to scan and compute
SELECT * FROM Orders WHERE YEAR(order_date) = 2024;
-- SARGable — the raw column is compared directly, so an index on order_date can be used
SELECT * FROM Orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
Both queries return exactly the same rows. Only the second one can actually use an index efficiently on a large table — the first forces a full scan, computing YEAR() for every single row before it can even check the condition, because the index has no entry for "the year part of this date," only for the date itself.
The same trap shows up with arithmetic and string functions on the filtered column:
-- NOT SARGable — salary * 1.1 must be computed per row before comparing
WHERE salary * 1.1 > 66000;
-- SARGable — the column stays raw; do the math to the constant instead
WHERE salary > 66000 / 1.1;
-- NOT SARGable — LOWER(name) can't be looked up in an index sorted on the raw name
WHERE LOWER(name) = 'alice';
-- Better: a case-insensitive collation on the column, or a dedicated
-- functional/expression index on LOWER(name), where the engine supports one
A functional index (also called an expression index; supported by Postgres, and available in some form on most modern engines) is the correct escape hatch when you genuinely need to search on a transformed value routinely — it indexes the result of the expression itself, so WHERE LOWER(name) = 'alice' becomes SARGable again if there's an index specifically on LOWER(name). It's a deliberate, explicit trade-off, not something to reach for casually, since it's another structure the database must maintain on every write.