What makes a predicate sargable, and what are the most common ways to accidentally make a predicate non-sargable?

How to think about it

Sargability is the gateway to index performance. A sargable predicate lets the engine walk straight to the matching key range in the B-tree. A non-sargable predicate forces a full scan — evaluating a computation on every single stored row — even when a perfect index exists. It’s one of the most common sources of slow queries in production.

The mental test

Ask: “Can the database walk directly to the matching rows in the index without computing anything per stored row?”

• Yes → sargable (index seek).
• No → not sargable (full scan or index scan, not seek).

The five most common non-sargable patterns — and their fixes

1. Function wrapped around the indexed column

-- BAD: evaluates DATE() on every row
SELECT * FROM logs WHERE DATE(created_at) = '2024-06-01';

-- GOOD: push the transform to the literal side
SELECT * FROM logs
WHERE created_at >= '2024-06-01'
  AND created_at <  '2024-06-02';

2. Arithmetic on the indexed column

-- BAD: computes balance * 1.1 for every row
SELECT * FROM accounts WHERE balance * 1.1 > 10000;

-- GOOD: rearrange so the column is bare
SELECT * FROM accounts WHERE balance > 10000 / 1.1;

3. Leading wildcard in LIKE

-- BAD: can't use index, must read every leaf node
SELECT * FROM products WHERE name LIKE '%widget%';

-- GOOD: prefix match uses the index
SELECT * FROM products WHERE name LIKE 'widget%';

-- For mid-string search, use full-text search (GIN/tsvector in PostgreSQL)
-- instead of LIKE

4. Implicit type cast

-- BAD: user_id is INTEGER but literal is VARCHAR
-- engine casts every stored integer to string before comparing
SELECT * FROM users WHERE user_id = '42';

-- GOOD: match types
SELECT * FROM users WHERE user_id = 42;

5. NOT IN / NOT LIKE on large sets

-- Usually non-sargable on the inner table; scans refunds for every outer row
SELECT * FROM orders
WHERE id NOT IN (SELECT order_id FROM refunds);

-- GOOD: rewrite as an anti-join
SELECT o.* FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
WHERE r.order_id IS NULL;

Expression indexes as an escape hatch

If you genuinely need to filter on a transformed value and can’t rewrite the predicate, create an index that stores the transformed value:

-- Index on the lowercased form
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Now this predicate IS sargable (the index stores LOWER(email))
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

How to confirm with EXPLAIN

After rewriting, check the plan:

-- Non-sargable: you'll see
Filter: (date(created_at) = '2024-06-01')   ← full scan, filter applied after

-- Sargable: you'll see
Index Cond: (created_at >= '2024-06-01' AND created_at < '2024-06-02')  ← seek ✓