Index-Friendly (Sargable) Predicates

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.

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.

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 ....

Quick check: 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
• The function changes the column, so the index order on raw values cannot be used directly