We Don't Do Functional Indexes. Here's Why We Made One Anyway.

We avoid functional indexes and refuse to add an index for every slow query. A vendor-generated Keycloak query took 1.1 s and 2.27 million buffer hits to return zero rows. After checking every cheaper option, we added one (name, lower(value)) index. Result: 0.2 ms and 11 buffers. Below is why the rule exists, why we broke it, and the checklist we now use for exceptions.

The rules

Below are a couple of rules we rarely break:

1. No function-based (expression) indexes.
2. No new index for every slow query.

Last week we broke both, on purpose, for one index on a table we don't own.

Why we have them

Every index has a cost on every write, not only on the read it was built for. Each INSERT and each UPDATE to an indexed column means another B-tree insertion, more WAL, more vacuum work and more storage. One index is cheap. Thirty indexes on a busy table are not, and that's where you end up if every slow query gets its own index.

Functional indexes add their own problems:

• They only match an identical expression. An index on lower(value) does nothing for upper(value), value ILIKE ..., or lower(trim(value)). One small change to the query and the index silently stops being used, but you keep paying for it on every write.
• They hide the real problem. If code wraps a column in a function, the usual fix is to normalize the data or change the query.
• On vendor schemas they cause upgrade problems. If the application manages its own schema (Liquibase, Flyway), your extra index can conflict with the vendor's next migration.

So when a developer asked for this, my first answer was no:

CREATE INDEX CONCURRENTLY idx_user_attrs_name_lower_value ON user_attributes (name, lower(value)); 

My exact words were roughly: "Using lower() is not a good query. Wrapping the column in lower() on the left side of the WHERE clause bypasses the index on value. That's a query problem, not an indexing problem. Is there a reason to think the stored values aren't already lowercase? If they are, drop the lower(). We avoid functional indexes."

The query

The table is Keycloak's user-attribute table. I've simplified the table, index and column names here, but the shape is the real one. The query searches for a user by a custom attribute:

SELECT DISTINCT u.* FROM users u LEFT JOIN user_attributes a ON u.id = a.user_id WHERE a.name = ? AND lower(a.value) = ? AND u.tenant_id = ? ORDER BY u.username OFFSET ? ROWS FETCH FIRST ? ROWS ONLY; 

The only existing index on these columns is (name, value), which can't serve lower(value) = ?.

The developer's answer to my question: we don't write this query. Keycloak does. The Admin REST API's "exact" attribute search is case-insensitive by design, so Keycloak's JPA layer wraps the column in lower(). No API flag turns that off. Normalizing the data to lowercase doesn't help either, because the predicate still has lower() around the column.

That ruled out the usual fix of changing the query. Sometimes if the design permits, we also create an additional lowercase column while preserving the input value in the original column.

My first test proved nothing

I ran EXPLAIN ANALYZE with an attribute I picked at random:

Bitmap Index Scan on idx_user_attrs_name_value (actual rows=11) Execution Time: 0.112 ms 

That looked fine. But the attribute I picked had 11 rows in the table. The attribute that actually caused problems in our user migration had about 388,000.

The worst case

Same query, the high-volume attribute, and a value that doesn't exist (the normal case during migration, where most lookups miss):

Limit (cost=1230.03..10786.46 rows=100) (actual time=1124.919..1127.088 rows=0) Buffers: shared hit=2268828 -> Unique (rows=1936) -> Nested Loop -> Parallel Index Scan on users (username order) (actual rows=131677 loops=3) -> Index Scan using idx_user_attrs_user_id on user_attributes (actual rows=0 loops=395031) Filter: name = '...' AND lower(value) = '...' Rows Removed by Filter: 4 Execution Time: 1127.156 ms 

That's 1.1 seconds and 2.27 million buffer hits (about 17 GB of page reads) to return zero rows.

Limit (cost=1230.03..10786.46 rows=100 width=739) (actual time=1124.919..1127.088 rows=0 loops=1) Buffers: shared hit=2268828 -> Unique (cost=1230.03..186242.65 rows=1936 width=739) (actual time=1124.917..1127.087 rows=0 loops=1) -> Gather Merge (cost=1230.03..186179.73 rows=1936 width=739) Workers Planned: 2 Workers Launched: 2 -> Incremental Sort (cost=230.00..184956.25 rows=807 width=739) Sort Key: u.username, u.id, ... Presorted Key: u.username -> Nested Loop (cost=0.85..184919.93 rows=807 width=739) Buffers: shared hit=2268672 -> Parallel Index Scan using uq_users_tenant_username on users u (actual time=0.029..135.635 rows=131677 loops=3) Index Cond: (tenant_id = '<tenant-id>') Buffers: shared hit=398390 -> Index Scan using idx_user_attrs_user_id on user_attributes a (actual time=0.007..0.007 rows=0 loops=395031) Index Cond: (user_id = u.id) Filter: ((name = '...') AND (lower(value) = '...')) Rows Removed by Filter: 4 Buffers: shared hit=1870282 Planning Time: 0.360 ms Execution Time: 1127.156 ms 

Why the planner chose this plan

Look at rows=1936. That's 388,271 × 0.005. With no statistics on lower(value), PostgreSQL falls back to its default equality selectivity of 0.5%.

So the planner thought: "About 1,936 users match. I need the first 100 in username order. I'll walk users in username order and stop after about 5% of the tenant's users."

You can check this against the cost numbers: 1230 + (186242 − 1230) × 100 / 1936 ≈ 10786, which is exactly the Limit cost.

That's a sensible plan if the estimate is right. When the value doesn't exist, the walk never stops early. It reads all ~395,000 users and probes their attributes one by one. When a lookup does hit, it still reads on average half the tenant's users.

A few details mattered:

• This happened with literal values. It wasn't only a generic-plan problem with prepared statements. The custom plan was just as bad.
• OFFSET made no difference. OFFSET 0 and OFFSET 100 gave the same plan and the same cost.
• It scales with the table. As the attribute grows, the default guess grows too, so the planner gets more confident in the wrong plan.
• Buffers show where the work went. About 82% of the 2.27M buffers came from probing attributes for users that never matched.

At 1.1 s on an idle database with 2 parallel workers per query, a burst of concurrent misses during migration pushed lookups to 9–53 seconds. Clients timed out and retried, and the abandoned queries kept running.

Every alternative we tried first

We went through the cheaper options before accepting an index:

Nothing short of the index turned this into a point lookup.

After

CREATE INDEX CONCURRENTLY idx_user_attrs_name_lower_value ON user_attributes (name, lower(value)); ANALYZE user_attributes; 

Nested Loop (actual rows=0) -> Index Scan using idx_user_attrs_name_lower_value on user_attributes Index Cond: (name = '...' AND lower(value) = '...') Buffers: shared hit=3 -> Index Scan using users_pkey on users (never executed) Execution Time: 0.218 ms 

The index gives two things: an access path and statistics. ANALYZE collects statistics on indexed expressions, so the planner stopped guessing and started from the attribute side by itself.

We also found that the Keycloak maintainers have been working on the same thing: an open upstream PR adds this exact (name, lower(value)) index to Keycloak's schema. We weren't adding something odd. We were filling a gap in the vendor schema that upstream is already closing.

The exception checklist

The rules stay. A functional index now has to pass all of these:

1. The SQL can't be changed. It's vendor or ORM generated, and no config or upstream fix is available on our version.
2. It's a hot path, with a stated call rate and latency requirement.
3. There's evidence of a real problem. EXPLAIN (ANALYZE, BUFFERS) on the worst case (high-volume values, misses, generic plans), not a convenient example.
4. Cheaper fixes were tried and measured: statistics, plan settings, query or data changes.
5. It's documented and reviewed:

COMMENT ON INDEX idx_user_attrs_name_lower_value IS 'Exception: vendor SQL uses lower(value) | owner: DBA | review at next Keycloak major upgrade'; 

What I'd tell my past self

• Test the worst case, not the first example that comes to mind. My 0.1 ms test was real, but it only covered the easy case.
• Read buffers, not just timing. Going from 2.27M buffers to 11 is evidence that holds whether the server is busy or idle.
• Treat rules as defaults. "No functional indexes" protected us from dozens of bad indexes. Making this one exception, with evidence and a review date, keeps the rule credible.

How does your team decide when an index is worth its write cost? I'd like to hear your rules in the comments.