Indexes & Query Performance

Sargability and Query Shape

A predicate is sargable when it can become a search argument for an access method, rather than merely filtering rows after retrieval. Preserve indexed expressions, compatible types, bounded ranges, and indexable Boolean branches without changing query semantics.

Functions on the indexed column can hide its order.
Implicit conversion direction matters.
Leading wildcards lack a B-tree prefix.
Arithmetic should isolate the column.
OR requires usable branches.
Sargable does not mean faster.

Before and semantics-preserving direction
Shape that can block search	Candidate rewrite / structure
`CAST(placed_at AS date) = DATE '2026-05-01'`	`placed_at >= start AND placed_at < next_day` in the model's local timestamp convention
`CAST(order_id AS text) = :text_id`	Bind `:id` as the column type
`sku LIKE '%ABC'`	Trigram/reverse-key/suffix design if truly required
`quantity * unit_price > 100`	Usually retain the expression or deliberately index that exact expression
`customer_id=7 OR status='PENDING'`	Indexes for both branches or justified `UNION`; retain set semantics

Timestamp and prefix rewrites

-- Before: applies a cast to every candidate timestamp.
WHERE CAST(placed_at AS date) = DATE '2026-05-01'

-- After: half-open boundaries in the canonical local/naive timestamp model.
WHERE placed_at >= TIMESTAMP '2026-05-01 00:00:00'
  AND placed_at <  TIMESTAMP '2026-05-02 00:00:00'

-- Prefix, subject to matching collation/operator class.
WHERE sku LIKE 'ABC%';

OR shape with explicit set semantics

-- UNION removes duplicates like the original OR; UNION ALL would not.
SELECT order_id FROM orders WHERE customer_id = 7
UNION
SELECT order_id FROM orders WHERE status = 'PENDING';
-- Compare with the original plan: PostgreSQL may already combine the two declared predicates with BitmapOr.