📺 Prefer to watch? 90-second YouTube Short · 💬 Telegram
Originally published on software-engineer-blog.com.
You have an index on email. You write what looks like a perfectly ordinary case-insensitive lookup:
SELECT * FROM users
WHERE LOWER(email) = '[email protected]';
And it takes seconds. EXPLAIN says Seq Scan on users. The index is right there, and the database is refusing to use it.
This is not a planner bug, and the index is not "disabled". It is a value mismatch, and once you see it you cannot unsee it.
Mental model: A B-tree index is a sorted copy of raw column values; applying a function to the column creates a different value that the index does not contain, so the planner must scan every row instead.
What a B-tree index actually stores
A B-tree index on email is a sorted tree whose leaves hold the value exactly as you stored it:
When your query asks for LOWER(email) = '[email protected]', the planner checks whether it can navigate the tree using that predicate. But the tree is keyed on the raw email values. The lowercase version—[email protected]—is nowhere in the leaves. So the planner cannot use the index. It does the only thing left: read every row from disk, compute LOWER(email) for each one, and compare.
On a table with five million rows, that is five million function calls per query. On a write-heavy table, you pay this cost every read.
The fix: expression indexes
An expression index is not a new index type. It is the same B-tree, but keyed on the result of a function instead of a raw column:
CREATE INDEX idx_lower_email ON users (LOWER(email));
Now the leaves hold the pre-computed lowercase values:
When your query asks for LOWER(email) = '[email protected]', the planner finds that exact value in the tree. One B-tree lookup. No sequential scan.
The expression is computed once, when the row is written—on insert or update. The cost is paid upfront, at write time, not amortized across millions of reads.
The one rule that decides if it works
The expression in the index must match the expression in the query exactly.
If you have:
CREATE INDEX idx_lower_email ON users (LOWER(email));
Then WHERE LOWER(email) = '[email protected]' uses it. But WHERE UPPER(email) = '[email protected]' does not—different function, different tree, back to a scan.
The same principle applies to any expression:
-
(data->>'email')— a JSONB key extraction -
date_trunc('day', created_at)— a timestamp truncation -
lower(trim(email))— a chained function -
extract(year from order_date)— date arithmetic
If your WHERE clause computes it, you can index it. If the index expression does not match the query expression, the planner will not use it.
The trade-off: work moves, not vanishes
| Aspect | Regular Index | Expression Index |
|---|---|---|
| Read cost | Sequential scan + function on every row | One B-tree lookup |
| Write cost | Store raw value | Compute function, store result, maintain index |
| Storage | Column value only | Expression result + index overhead |
| When to use | Occasional lookups, write-heavy workload | Frequent lookups on the same expression, read-heavy workload |
The work does not disappear. It moves from read time to write time. Every INSERT or UPDATE now runs the expression before writing to disk. In exchange, reads that match that expression become instant.
This trade-off is worth it when:
- You have a read-heavy table.
- You run the same expression in the WHERE clause frequently.
- The cost of computing the expression is non-trivial (like a function call or JSON extraction).
It is not worth it when:
- The table is write-heavy and rarely queried with that expression.
- The expression is cheap (like a simple arithmetic operation).
- The table is already under write pressure.
For LLM inference workloads
If you are serving embedding lookups or vector searches via a relational database (common in RAG and agent frameworks), expression indexes become critical. You often normalize text before embedding lookup:
WHERE LOWER(TRIM(query_text)) = model_input
Without an expression index, every inference request triggers a full table scan. With one, you move the normalization cost to index write time—a one-time hit per new document ingested, then negligible read latency per inference. On high-throughput inference services, this can be the difference between TTFT under 100ms and over 500ms.
Verdict
Reach for a regular index when lookups are rare or writes are frequent; reach for an expression index when you query the same computed value repeatedly and read traffic dominates. The key constraint: the index expression and the query WHERE clause must match exactly, character for character.
Watch the 90-second reel on Instagram or YouTube for the visual walkthrough: index leaves, the value mismatch, the sequential scan, and the expression index fix drawn in real time.
Top comments (0)