Function-Wrapped Columns and Sargability #

Three predicates, three sequential scans: WHERE lower(email) = $1, WHERE created_at::date = current_date, and WHERE id::text = $1. Each column is indexed. None of the indexes can be used, because in every case the index stores the column’s values and the query asks about something computed from them.

Where predicates are evaluated is covered in filter pushdown mechanics. This page covers the condition that decides whether a predicate can reach an index at all.

The Planner Condition #

A predicate is sargable — usable as an index condition — when it compares an indexed expression with something the planner can evaluate independently of the row. In practice that means the indexed column (or exactly the indexed expression) appears on one side, unwrapped, and the other side is a constant, a parameter, or a value from an outer row in a nested loop.

Wrapping the column breaks the match:

Not sargable Why Sargable rewrite
lower(email) = $1 index holds email, not lower(email) expression index, or store normalised
created_at::date = current_date cast produces a different type and value created_at >= current_date AND created_at < current_date + 1
id::text = $1 cast on the indexed column compare as the column’s type
total * 1.2 > 100 arithmetic on the column total > 100 / 1.2
extract(year from d) = 2026 function on the column range on d
col LIKE '%x%' no anchored prefix trigram index

Two properties of the planner make this strict. First, it matches expressions syntactically against index definitions, not semantically: it does not know that created_at::date = current_date describes a contiguous range. Second, the resulting Filter is applied per row after the scan, so the predicate still works — it is just evaluated the expensive way, and its selectivity is estimated from defaults, as described in default selectivity for unestimable predicates.

The three fixes, in order of preference: rewrite the predicate so the column stands alone; add an expression index matching the predicate exactly; or store the computed value in a generated column and index that.

Choosing the fix Three options. Rewriting the predicate as a range or a direct comparison keeps the existing index and is always preferable. An expression index matching the predicate exactly works when a rewrite is impossible. A stored generated column with an ordinary index gives statistics and index-only scans at the cost of storage. predicate wraps the column in a function or cast equivalent range exists rewrite the predicate uses the existing index no rewrite possible expression index must match exactly value used often generated column real stats, index-only scans rewriting is free; the other two add objects to maintain

Annotated EXPLAIN Evidence #

A cast on a timestamp:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE placed_at::date = current_date;
Aggregate  (actual time=1804.2..1804.2 rows=1 loops=1)
  ->  Seq Scan on orders  (cost=0.00..412008.00 rows=470110 width=0)
                          (actual time=0.05..1798.4 rows=18402 loops=1)
        Filter: ((placed_at)::date = CURRENT_DATE)
        Rows Removed by Filter: 94003708
        Buffers: shared hit=41022 read=71190
Execution Time: 1804.6 ms
-- rows=470110 is 0.5% of the table: a default estimate, not a measurement

Rewritten as a range:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders
WHERE placed_at >= current_date AND placed_at < current_date + 1;
Aggregate  (actual time=4.2..4.2 rows=1 loops=1)
  ->  Index Only Scan using orders_placed_at_idx on orders  (actual rows=18402 loops=1)
        Index Cond: ((placed_at >= CURRENT_DATE) AND (placed_at < (CURRENT_DATE + 1)))
        Heap Fetches: 0
        Buffers: shared hit=74
Execution Time: 4.3 ms

And the expression-index route for a case with no rewrite:

CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));
ANALYZE users;
Index Scan using users_lower_email_idx on users  (actual time=0.03..0.03 rows=1 loops=1)
  Index Cond: (lower(email) = '[email protected]'::text)
  Buffers: shared hit=4
-- the predicate must match the index expression exactly, including the function and its arguments
Filter or Index Cond? Three lines are annotated. A Filter containing a function applied to a column shows the predicate could not reach the index. A default-looking row estimate shows the planner had no statistics for the expression. An Index Cond containing the same expression shows an expression index matched it. Filter: ((placed_at)::date = CURRENT_DATE) wrapped column: not sargable rows=470110 (0.5% of the table) default estimate for the expression Index Cond: (lower(email) = '…') expression index matched the same predicate text under Index Cond means it reached the index

Step-by-Step Resolution #

  1. Search plans for functions and casts inside Filter lines. Anything of the form f(column) or (column)::type compared with a value is a candidate.

  2. Try a rewrite first. Most date and numeric cases have an exact range equivalent:

    -- month
WHERE created_at >= date_trunc('month', now()) AND created_at < date_trunc('month', now()) + interval '1 month'
-- prefix search instead of lower(x) LIKE
WHERE name >= 'Han' AND name < 'Hao'

  3. Be careful with time zones. placed_at::date uses the session TimeZone for a timestamptz column, so the equivalent range must use the same zone explicitly: placed_at >= (current_date AT TIME ZONE 'UTC').

  4. Where no rewrite exists, add an expression index and analyze, so both the access path and the statistics improve, following expression statistics for computed predicates.

  5. For values used in many queries, store them. A generated column keeps the value in sync and behaves like any other column:

    ALTER TABLE users ADD COLUMN email_normalized text GENERATED ALWAYS AS (lower(email)) STORED;
CREATE INDEX CONCURRENTLY users_email_norm_idx ON users (email_normalized);

  6. Standardise the predicate across the codebase. An expression index only matches queries that write the expression the same way; a shared helper or a generated column prevents drift.

  7. Re-check the plan for Index Cond rather than Filter, and confirm the estimate is no longer a default.

Four spellings, same result Casting the column to date and comparing takes 1,805 milliseconds via a sequential scan. An expression index on the cast takes 6 milliseconds. A rewritten range on the raw column takes 4 milliseconds. A generated date column with its own index takes 4 milliseconds and also supports grouping. placed_at::date = today 1,805 ms expression index on cast 6 ms rewritten as a range 4 ms generated day column 4 ms the rewrite needs no new object at all

Before and After #

-- BEFORE: cast on the indexed column
Seq Scan on orders  Filter: ((placed_at)::date = CURRENT_DATE)  Rows Removed: 94003708   Execution Time: 1804.6 ms

-- AFTER: equivalent range predicate
Index Only Scan  Index Cond: (placed_at >= CURRENT_DATE AND placed_at < CURRENT_DATE + 1)  Execution Time: 4.3 ms

Why the planner will not rewrite it for you #

It is tempting to expect the planner to recognise that placed_at::date = current_date means a one-day range. It does not, and the reason is generality: the cast’s behaviour depends on the session time zone, on the types involved, and on functions that may not be monotonic. Proving that a given expression is equivalent to a range requires knowledge the planner does not have for arbitrary functions, and getting it wrong would return incorrect results.

Some databases implement narrow special cases for common functions; PostgreSQL’s approach is to let you declare the equivalence yourself, either by writing the range or by indexing the expression. That is more work at the point of writing a query and far more predictable, because a predicate’s cost is visible in the plan rather than depending on whether the planner happened to recognise a pattern.

The practical consequence is a habit worth teaching: keep the column bare on one side of the comparison. Almost every sargability problem is an instance of that rule being broken, and almost every fix is an application of it.

Common Pitfalls #

Time-zone drift in rewrites. ::date on timestamptz depends on the session zone. Diagnostic signal: different results between application and batch sessions. Fix: make the zone explicit in both predicate and index.

Expression index that does not match. A different spelling of the same expression is a different expression. Diagnostic signal: the index exists and is unused. Fix: align the query text with the index definition.

Casting parameters instead of columns. id = $1::bigint is fine; id::text = $1 is not. Diagnostic signal: a cast on the column side. Fix: bind the parameter with the column’s type.

Indexing every expression. Each costs writes. Diagnostic signal: several expression indexes on one column. Fix: a generated column, or normalise the data.

Frequently Asked Questions #

What does sargable mean? #

It describes a predicate that can be used as an index condition — one where the indexed column or expression appears unwrapped on one side of a comparison. Wrapping the column in a function or cast makes the predicate non-sargable, so it becomes a filter applied after the scan.

Why does casting a timestamp to date prevent index use? #

The index stores timestamp values, and the query asks about a derived date value, which is a different expression. PostgreSQL matches expressions syntactically, so it cannot use the timestamp index unless you rewrite the predicate as a range or index the cast.

Is an expression index as good as rewriting the query? #

It restores index use and provides statistics, but it costs storage and write time, and it only matches queries that write the expression identically. A rewrite that uses the existing index is preferable when one exists.

Up: Filter Pushdown Mechanics