Database performance: Using functional indexes for faster queries

Last month, our search endpoint started timing out during peak traffic. We were running SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com'), and as the table hit 4 million rows, the sequential scans became a bottleneck. It was a classic case where standard indexes couldn't help because we were wrapping our columns in a function.

If you've ever dealt with similar latency spikes, you know that database performance often dies at the hands of poorly optimized string comparisons. I want to show you how to move away from expensive runtime transformations by leveraging functional indexes.

Why functional indexes change the game

When you index a column directly, the database stores the raw values in a B-tree. If you query that column using a function—like LOWER(), UPPER(), or even a date truncation—the query planner ignores the index. It has to scan every single row, apply the function, and then check for a match.

A functional index (or expression index) allows you to index the result of an expression. Instead of indexing the email column, you index the LOWER(email) result. When the query planner sees your LOWER(email) predicate, it matches the index perfectly.

Implementing functional indexing in PostgreSQL

Let's look at the implementation. In PostgreSQL 15, creating an index on an expression is straightforward.

SQL
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

Now, when you run your search query:

SQL
EXPLAIN ANALYZE 
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

You'll see an "Index Scan" instead of a "Seq Scan." In our case, this reduced the query latency from roughly 450ms down to about 2ms. It’s a massive win for a single line of SQL.

Beyond simple strings: Expression-based filtering

Functional indexes aren't just for string normalization. You can use them for any deterministic expression. If you frequently filter by the year of a created_at timestamp, indexing the column directly is often inefficient because the index is too large and the search space is too broad.

Instead, try this:

SQL
CREATE INDEX idx_users_created_year ON users (EXTRACT(YEAR FROM created_at));

This index is tiny compared to the full created_at index. It only stores the year, making it incredibly fast to traverse.

However, be careful with query optimization here. If your application logic changes or if you start querying by month or day, this specific index becomes useless. I’ve made the mistake of over-indexing every possible extraction, which eventually slowed down our INSERT and UPDATE operations because the database had to maintain too many secondary structures.

The trade-offs and "gotchas"

Everything has a cost. Every time you insert a new row, the database engine has to compute the function and update the index. If you have a write-heavy workload, adding five different functional indexes will noticeably increase your write latency.

Before you go overboard, consider these points:

1. Deterministic results only: You cannot use functions like RANDOM() or NOW() in an index. The database needs the result to be consistent every time the row is evaluated.
2. Maintenance overhead: If you already use partial indexes for high-cardinality filtering, think about whether a functional index is a better fit. Sometimes, a partial index on a boolean flag is cheaper than a functional index on a string.
3. Storage: Functional indexes take up disk space. If you're working with millions of rows, that index will grow.

When to use other strategies

Functional indexes are powerful, but they aren't the only tool in your belt. If you're struggling with SQL indexing for JSONB data, you might be better served by database schema optimization: Indexed Generated Columns for JSONB. Generated columns essentially bake the function into the table schema itself, which can be more readable for other developers on your team.

Also, keep in mind that database indexing strategies: Mastering composite indexes for speed are usually the first thing you should check before resorting to functional indexes. If you can satisfy a query with a standard composite index, do that first. It’s almost always more flexible.

Frequently Asked Questions

Does a functional index work if I change the function in my query? No. If your index is on LOWER(email), but your query uses UPPER(email), the index won't be used. The expressions must match exactly.

Can I use functional indexes with LIKE queries? Yes, but you need the right operator class. For case-insensitive LIKE queries (e.g., email LIKE 'user%'), you'll need the pg_trgm extension and a GIN index on the function result. A standard B-tree index won't support prefix searching on a transformed string.

Is there a limit to how many functional indexes I can create? There’s no hard limit, but practically, you're limited by the performance impact on your writes. Test your INSERT speeds after adding multiple indexes.

I’m still experimenting with how functional indexes interact with table partitioning. In some of our newer services, we’ve found that partitioning the table first makes the functional index even faster because the index itself is smaller. It’s a balancing act, and I’m definitely still learning where the line is between "helpful optimization" and "unnecessary bloat."