Writing index-friendly (sargable) conditions

A condition is sargable, short for search-argument-able, when the database can answer it by searching an index instead of testing every row. An index keeps a column’s values in sorted order, so it can narrow a search only when the condition compares the bare column with a value that is known before the search starts. placed_at >= '2026-03-15' AND placed_at < '2026-03-16' is sargable. date(placed_at) = '2026-03-15' asks the same question, but the column sits inside a function, and the index is not sorted by that function’s result, so the engine has to compute it for every row. Keeping the column bare is often the cheapest speed-up SQL offers: no new index, no bigger server, and exactly the same rows.

Why a function on the column turns a search into a scan

The PostgreSQL manual states the rule for its B-tree indexes: the planner considers one when an indexed column is compared using <, <=, =, >= or >. SQLite’s optimizer documentation lists the same shapes, a column on one side and an expression on the other, as the terms an index can use. A comparison on date(placed_at) has no bare column in it, whatever the function does. The script below asks both questions of 20,000 orders with an index on placed_at:

-- 20,000 orders placed over 90 days, stored as UTC text ('YYYY-MM-DD HH:MM:SS'),
-- and an index on the moment each order was placed.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  placed_at    TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO orders
SELECT i, 1 + (i * 37) % 2000,
       strftime('%Y-%m-%d %H:%M:%S', '2026-01-01', '+' || (i * 397 % 7776000) || ' seconds'),
       100 + (i * 7919) % 90000
FROM n;
CREATE INDEX orders_placed_at ON orders (placed_at);

-- One question, asked two ways: which orders were placed on 2026-03-15?
-- 1. The column is inside a function.
EXPLAIN QUERY PLAN
SELECT id, amount_paise FROM orders
WHERE date(placed_at) = '2026-03-15';

-- 2. The bare column is compared with the two ends of the day.
EXPLAIN QUERY PLAN
SELECT id, amount_paise FROM orders
WHERE placed_at >= '2026-03-15' AND placed_at < '2026-03-16';

-- Both forms find the same orders.
SELECT
  (SELECT count(*) FROM orders
    WHERE date(placed_at) = '2026-03-15') AS with_function,
  (SELECT count(*) FROM orders
    WHERE placed_at >= '2026-03-15' AND placed_at < '2026-03-16') AS with_range;

┌────┬────────┬─────────┬─────────────┐
│ id │ parent │ notused │   detail    │
├────┼────────┼─────────┼─────────────┤
│ 2  │ 0      │ 216     │ SCAN orders │
└────┴────────┴─────────┴─────────────┘
┌────┬────────┬─────────┬──────────────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                                  detail                                  │
├────┼────────┼─────────┼──────────────────────────────────────────────────────────────────────────┤
│ 3  │ 0      │ 165     │ SEARCH orders USING INDEX orders_placed_at (placed_at>? AND placed_at<?) │
└────┴────────┴─────────┴──────────────────────────────────────────────────────────────────────────┘
┌───────────────┬────────────┐
│ with_function │ with_range │
├───────────────┼────────────┤
│ 217           │ 217        │
└───────────────┴────────────┘

SCAN orders means that SQLite read every row and computed date() on each. SEARCH orders USING INDEX means that it jumped to the first entry of the day, read entries until the next day began, and stopped: 217 entries. The plan prints > and < for >= and <= alike. Both forms count the same 217 orders, which is the other half of every rewrite: it must search the index and return identical rows.

Most slow filters hide the column in one of a few ways, and each has a rewrite:

• Functions on the column, such as date(), lower() or substr(): write a range on the bare column, or index the expression itself.
• Arithmetic on the column, such as amount_paise / 100 = 500: move the arithmetic to the constant side, keeping the rounding of integer division.
• Casts and implicit conversions: compare a column with a value of its own type, because converting every value of the column defeats its index.
• Leading wildcards, as in LIKE '%@example.org': an index sorted from the first character cannot find endings, so index the part people search for.
• Case and collation mismatches: a case-insensitive comparison needs an index built with the same collation, or an index on the same expression.
• OR across different columns: every column in the OR needs an index of its own, and the engine then combines the searches.
• IS NULL belongs on no such list: an ordinary B-tree index can search for NULLs in SQLite, PostgreSQL and MySQL.

A rewrite counts only when the plan improves and the rows stay identical, so check both every time.

Dates and times: half-open ranges that keep every row Pro: opens with a Pro, Premium or Ultimate pass