Definition
- Sargable – Short for Search ARGument ABLE; a predicate where the indexed column appears bare on one side and is compared to a constant or range, allowing the optimizer to seek directly into the B-tree.
- Non-sargable – A predicate where a function, arithmetic, or implicit cast wraps the indexed column, forcing per-row evaluation and a full scan.
- Half-open range rewrite – Converting
YEAR(created_at) = 2026tocreated_at >= '2026-01-01' AND created_at < '2027-01-01'so the bare column is compared directly. - Expression index – An index on a function result like
CREATE INDEX ON accounts(LOWER(email))that stores the transformed value, making the matching function query sargable. - Verification – Always confirm with
EXPLAIN ANALYZEthat the plan now shows Index Scan rather than Seq Scan.
The National Bank of India ledger index is sorted by the raw created-at timestamp. A sargable question is "give me everything from 1 January 2026 up to but not including 1 January 2027" – the clerk opens the index at the first date and reads a clean range. A non-sargable question is "give me everything where the YEAR of the date equals 2026" – now the clerk must compute the year of every single row before deciding, because the index is sorted by full dates, not by extracted years. Same answer, but one lets the clerk use the sorted shortcut and the other forces reading the whole ledger.
Understanding the Concepts
What is a sargable predicate?
A sargable predicate is one the optimizer can satisfy by seeking directly into a B-tree index. The indexed column appears untouched on one side of the comparison, and the other side is a constant or a range boundary.
-- Sargable: bare column compared to constants
WHERE account_id = 'SAV2001' -- equality seek
WHERE created_at >= '2026-01-01' -- range seek
WHERE amount BETWEEN 10000.00 AND 50000.00 -- range seek
WHERE customer_name LIKE 'Raj%' -- prefix seek
-- NOT sargable: function/arithmetic wraps the column
WHERE YEAR(created_at) = 2026 -- function on column
WHERE DATE(ts) = '2026-05-01' -- function on column
WHERE UPPER(email) = 'RAJESH@BANK.IN' -- function on column
WHERE balance + fee > 1000 -- arithmetic on column
WHERE SUBSTRING(account_id,1,3) = 'SAV' -- function on column
Why does a function on the column defeat the index?
A B-tree index is sorted by the raw column value. A seek works by navigating to a starting key and walking a contiguous range of leaves. When you wrap the column in a function, the engine must compute that function on every stored value before it can compare, so there is no contiguous key range to seek.
-- Index on transactions(created_at)
CREATE INDEX ix_txn_date ON transactions(created_at);
-- Non-sargable: YEAR() on the column
EXPLAIN ANALYZE
SELECT txn_id, amount
FROM transactions
WHERE YEAR(created_at) = 2026;
Seq Scan on transactions
Filter: (date_part('year', created_at) = 2026)
Rows Removed by Filter: 180,000,000
Planning Time: 0.2 ms
Execution Time: 45,230 ms -- 45 seconds!
How do you rewrite a function predicate as a sargable range?
The primary fix is a half-open range on the raw column. The lower bound uses >= and the upper bound uses < (strictly less than), which correctly includes every timestamp in the range without boundary bugs.
-- Rewrite: YEAR(created_at) = 2026
-- becomes a half-open range on the raw column
EXPLAIN ANALYZE
SELECT txn_id, amount
FROM transactions
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';
Index Scan using ix_txn_date on transactions
Index Cond: (created_at >= '2026-01-01'
AND created_at < '2027-01-01')
Planning Time: 0.1 ms
Execution Time: 85 ms -- 500x faster
More rewrite examples for National Bank of India queries.
-- DATE(ts) = '2026-05-01' -> half-open range
-- Wrong:
SELECT * FROM transactions WHERE DATE(created_at) = '2026-05-01';
-- Correct:
SELECT * FROM transactions
WHERE created_at >= '2026-05-01'
AND created_at < '2026-05-02';
-- MONTH(created_at) = 10 AND YEAR(created_at) = 2026
-- Wrong:
SELECT * FROM transactions
WHERE EXTRACT(MONTH FROM created_at) = 10
AND EXTRACT(YEAR FROM created_at) = 2026;
-- Correct:
SELECT * FROM transactions
WHERE created_at >= '2026-10-01'
AND created_at < '2026-11-01';
How do you use DATE_TRUNC to build sargable bounds?
In application code or SQL, use DATE_TRUNC to compute the bounds dynamically.
-- PostgreSQL: compute bounds dynamically
-- Lower bound: start of the month
-- Upper bound: start of the next month (exclusive)
SELECT txn_id, amount, created_at
FROM transactions
WHERE created_at >= DATE_TRUNC('month', '2026-10-15'::date)
AND created_at < DATE_TRUNC('month', '2026-10-15'::date)
+ INTERVAL '1 month';
// com.bank.service.StatementService
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.List;
public class StatementService {
/**
* Sargable monthly statement query.
* Uses half-open range instead of EXTRACT/YEAR functions.
*/
public List<String> getMonthlyStatement(
Connection conn, String accountId, YearMonth month)
throws SQLException {
LocalDate start = month.atDay(1);
LocalDate end = month.plusMonths(1).atDay(1);
String sql = """
SELECT txn_id, amount, created_at
FROM transactions
WHERE account_id = ?
AND created_at >= ?
AND created_at < ?
ORDER BY created_at
""";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setDate(2, Date.valueOf(start));
ps.setDate(3, Date.valueOf(end));
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
BigDecimal amount = rs.getBigDecimal("amount");
results.add(rs.getString("txn_id")
+ " | " + amount.toPlainString());
}
}
}
return results;
}
}
When do you use an expression index instead?
When a rewrite is genuinely not possible – for example, a case-insensitive email lookup – create an expression index that stores the transformed value.
-- Expression index on LOWER(email)
CREATE INDEX ix_accounts_email_lower
ON accounts(LOWER(email));
-- Query MUST use the identical function
EXPLAIN ANALYZE
SELECT * FROM accounts
WHERE LOWER(email) = 'rajesh@nationalbank.in';
Index Scan using ix_accounts_email_lower on accounts
Index Cond: (lower(email) = 'rajesh@nationalbank.in')
The transformation in the query and the index must be identical. LOWER() in the index but UPPER() in the query does not match.
How does implicit type coercion cause non-sargability?
When you compare a VARCHAR column to a numeric literal, the database may cast the column instead of the literal.
-- account_id is VARCHAR(20)
-- Comparing to an integer triggers implicit cast on the column
SELECT * FROM accounts WHERE account_id = 2001;
-- Internally: WHERE CAST(account_id AS INTEGER) = 2001
-- Result: Seq Scan (function on column defeats index)
-- Fix: match the literal type to the column type
SELECT * FROM accounts WHERE account_id = '2001';
-- Result: Index Scan (sargable)
Why must you verify with EXPLAIN ANALYZE?
After any rewrite, you must confirm the plan actually changed. An expression index that does not exactly match the query, or a leftover type cast, will silently keep the scan.
-- Before fix: check the plan
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE YEAR(created_at) = 2026;
-- Seq Scan: 45 seconds
-- After fix: verify the plan changed
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';
-- Index Scan: 85 ms
What are common mistakes?
The most common mistake is rewriting the query but forgetting to match the expression index.
-- Wrong: index is on LOWER(email) but query uses UPPER(email)
CREATE INDEX ix_email_lower ON accounts(LOWER(email));
SELECT * FROM accounts WHERE UPPER(email) = 'RAJESH@BANK.IN';
-- Still a Seq Scan! LOWER != UPPER
-- Correct: query matches the index function exactly
SELECT * FROM accounts WHERE LOWER(email) = 'rajesh@bank.in';
-- Index Scan on ix_email_lower
What are common interview follow-up questions?
- How does DATE_TRUNC help build sargable date ranges?
- What is the difference between a half-open range and a closed range for timestamps?
- How does implicit type coercion defeat an index?
- When is an expression index better than a range rewrite?
Visual Overview
┌─────────────────────────────────────────────────┐ │ Sargable vs Non-Sargable Predicates │ ├─────────────────────────────────────────────────┤ │ │ │ Sargable (index can seek): │ │ ┌───────────────────────────────────┐ │ │ │ WHERE created_at >= '2026-01-01' │ │ │ │ AND created_at < '2027-01-01' │ │ │ └───────────────┬───────────────────┘ │ │ ▼ │ │ B-tree: seek to '2026-01-01' ──► walk leaves │ │ Cost: O(log n) + range │ │ │ │ Non-Sargable (full scan): │ │ ┌───────────────────────────────────┐ │ │ │ WHERE YEAR(created_at) = 2026 │ │ │ └───────────────┬───────────────────┘ │ │ ▼ │ │ Must compute YEAR() on every row │ │ Cost: O(n) – full table scan │ │ │ │ Fix 1: Rewrite as half-open range │ │ YEAR(x) = 2026 │ │ ──► x >= '2026-01-01' AND x < '2027-01-01'│ │ │ │ Fix 2: Expression index │ │ CREATE INDEX ON tbl(LOWER(email)) │ │ WHERE LOWER(email) = ? ──► Index Scan │ └─────────────────────────────────────────────────┘
Key Takeaway
A sargable predicate, short for Search ARGument ABLE, is one the database can satisfy by seeking directly into an index because the indexed column sits bare on one side of the comparison and is matched against a constant or a range. An index is a B-tree sorted by the raw column value, so the engine can only seek when it can map your comparison onto that sorted order. The moment you wrap the column in a function, do arithmetic on it, or trigger an implicit cast, the predicate becomes non-sargable: the engine has to evaluate the expression on every row, and the index range scan collapses into a full table scan.
That is exactly why WHERE YEAR of created-at equals 2026, or DATE of a timestamp equals a day, or UPPER of email equals a value, all defeat a normal index. The index is sorted by full timestamps or raw emails, not by extracted years or uppercased strings, so there is no contiguous key range to walk. The primary fix is to rewrite the function predicate as a half-open range on the raw column: created-at greater than or equal to the first of January and strictly less than the first of the next January. The strictly-less-than upper bound cleanly includes the last instant and avoids timestamp boundary bugs. When a rewrite genuinely is not possible, such as case-insensitive email matching, create a function-based expression index and make sure the query uses the identical transformation.
At the National Bank of India the year-end tax report was written with YEAR of created-at, and over two hundred million transactions it pinned the database CPU for minutes because it scanned the whole table. Rajesh rewrote it as a half-open date range and it dropped to a fast index range scan. For the login path, which truly needs case-insensitive matching, he added an expression index on LOWER of email and matched the query to it.
The habit Pankaj never skips is verification. After every rewrite he runs EXPLAIN ANALYZE and confirms the plan now says Index Scan rather than Sequential Scan, because an expression index that does not exactly match the query, or a stray type cast, will quietly keep the scan, and the execution plan is the only honest proof that the fix actually worked.
Remember one sentence: keep the indexed column bare and compare it to a range – the moment you wrap it in a function, the index range scan dies.
---