# Index-Friendly (Sargable) Predicates — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/p-sargable

> Do not wrap indexed columns in functions.

## Compare the bare column with a range

An index on `created_at` stores raw values in order. A predicate that applies a function to the column (`date(created_at) = ...`, `lower(email) = ...`, `amount + 0 > 10`) cannot use that index directly and leads to a full scan. Rewrite as a **range on the bare column** (`created_at >= day AND created_at < next_day`), or create an **expression index** that matches the function exactly. Such predicates are called sargable (search-argument-able).

## Help the planner use indexes

Index-friendly predicates, the right composite index order and reading EXPLAIN make queries fast.

![Four ideas: sargable predicates, composite indexes, EXPLAIN ANALYZE, checklist.](assets/figures/sql-deep-dive/section-8-map.svg) — Figure 8.1 — Sargable predicates, composite indexes and EXPLAIN.

## A function on the column versus a range, run

I ran this with psql on PostgreSQL 16.2 against an events table of 200,000 generated rows with two indexes. The function version scans the whole table in parallel; the range version uses an index-only scan on events_created_idx and counts 1,440 events (one per minute of that day). The session time zone is set to UTC so timestamps print predictably.

```sql
SET TimeZone = 'UTC';
-- A function on the column hides it from the index
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE date(created_at AT TIME ZONE 'UTC') = '2026-03-01';

-- The same filter written as a range can use the index
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE created_at >= '2026-03-01 00:00+00' AND created_at < '2026-03-02 00:00+00';

SELECT count(*) FROM events
WHERE created_at >= '2026-03-01 00:00+00' AND created_at < '2026-03-02 00:00+00';
```

Output:

```
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Seq Scan on events
                     Filter: (date((created_at AT TIME ZONE 'UTC'::text)) = '2026-03-01'::date)
(6 rows)

                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using events_created_idx on events
         Index Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+00'::timestamp with time zone))
(3 rows)

 count 
-------
  1440
(1 row)
```

## Match expression indexes exactly

An index on lower(email) helps WHERE lower(email) = ..., not WHERE email ILIKE ....

**Quiz:** Why can WHERE date(created_at) = '2026-03-01' miss an index on created_at?

- [ ] PostgreSQL ignores indexes on timestamps
- [ ] Dates cannot be indexed
- [ ] The index is corrupted
- [x] The function changes the column, so the index order on raw values cannot be used directly

*Answer:* The function changes the column, so the index order on raw values cannot be used directly. Use a range or an expression index.