Indexes Kinds functional · LOWER · expression

Index on Expression

An index over a computed value rather than a raw column. Solves the case where queries always wrap a column in a function — like LOWER(email) for case-insensitive lookups.

What an index on expression is

An ordinary index stores values straight from a column. An index on expression (sometimes called a functional index) stores the result of a computation — for example, LOWER(email), (price * quantity), or EXTRACT(year FROM hire_date).

Why bother? Because PostgreSQL can only use an index when the query's filter matches the index's shape. If you index email but always query WHERE LOWER(email) = ?, the index isn't used. Indexing the same expression solves the mismatch.

Syntax

CREATE INDEX index_name
ON table_name (expression);

The expression must be deterministic — it has to return the same result every time it's called with the same arguments. Functions like RANDOM() or NOW() are off-limits.

Example 1 — case-insensitive lookup

The classic use case: case-insensitive email or username comparison.

SQL

CREATE TABLE users (
    user_id  SERIAL PRIMARY KEY,
    email    VARCHAR(100)
);

-- Plain index — won't help LOWER(email) queries
CREATE INDEX idx_users_email ON users (email);

-- Functional index — matches the query's shape
CREATE INDEX idx_users_email_lower
    ON users (LOWER(email));

-- This query now uses the functional index
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

💡 Match the query exactly. If the query is LOWER(email) = ?, the index has to be on LOWER(email) — same function, same arguments, same wrapping. Adding extra functions or whitespace doesn't matter, but a different function (e.g. UPPER) means a different index.

Example 2 — index on a date part

SQL

-- Speed up "show orders from a given year"
CREATE INDEX idx_orders_year
    ON orders (EXTRACT(YEAR FROM order_date));

-- Uses the index
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024;

For range queries on dates, you'd usually prefer an index on order_date directly with conditions like order_date >= '2024-01-01' AND order_date < '2025-01-01' — but if your application's query consistently uses EXTRACT, the functional index keeps it fast.

Example 3 — concatenation

SQL

-- "Search by full name" pattern
CREATE INDEX idx_students_full_name
    ON students3 ((first_name || ' ' || last_name));

SELECT * FROM students3
WHERE  first_name || ' ' || last_name = 'Alice Smith';

Note the double parentheses around the concatenation. PostgreSQL requires expressions involving operators to be wrapped — single-function expressions like LOWER(email) don't need them.

Example 4 — partial + functional

Combine an expression with a WHERE clause to index a computation only on selected rows.

SQL

-- Index lowercased emails of active users only
CREATE INDEX idx_active_email_lower
    ON users (LOWER(email))
    WHERE active = TRUE;

When to reach for a functional index

Pattern in your queries	Index to create
`WHERE LOWER(col) = ?`	Index on `LOWER(col)`
`WHERE TRIM(col) = ?`	Index on `TRIM(col)`
`WHERE col::date = ?`	Index on `(col::date)`
`WHERE EXTRACT(YEAR FROM col) = ?`	Index on `EXTRACT(YEAR FROM col)`
`WHERE col1 \|\| col2 = ?`	Index on `(col1 \|\| col2)`

Watch out for

Write cost. Every INSERT/UPDATE recomputes the expression to keep the index in sync. Cheap functions are fine; expensive ones (e.g. complex regex) cost noticeably more.
Function must be IMMUTABLE. If you wrap a custom function, mark it IMMUTABLE in its definition. Functions marked VOLATILE or STABLE can't be indexed.
Same expression, different SQL? The optimizer is reasonably clever, but doesn't normalize all expressions. LOWER(email) and lower(email) match (SQL is case-insensitive on identifiers), but LOWER(TRIM(email)) and TRIM(LOWER(email)) don't.

Recap

An index on expression stores the result of a computation, not the column itself.
Useful when queries always wrap a column in a function or transformation.
The query's expression must match the index's expression for the index to be used.
Combine with partial-index WHERE for further savings.
The expression must be deterministic and IMMUTABLE.