In this lesson, you will learn how to optimize query performance by indexing the results of expressions rather than raw column values.
The Problem: Computed Columns
When you write a query that involves a calculation, such as SELECT * FROM users WHERE LOWER(email) = 'bob@example.com', PostgreSQL cannot use a standard B-tree index on the email column. Because the index stores the raw strings, the database is forced to perform a sequential scan—calculating the LOWER() value for every single row in the table to check for a match. As your tables grow, this becomes a significant performance bottleneck.
The Solution: Functional Indexes
A functional index (or expression index) allows you to index the result of a function call. PostgreSQL essentially treats the output of that function as if it were a stored column. When you query using that same function, the optimizer sees the index and performs an index scan instead of a full table scan.
To create an index on the lowercase version of the email column, you use the following syntax:
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
Now, whenever you run a query using LOWER(email), the database uses the pre-computed values in idx_users_lower_email.
When to Use It
Functional indexing is not limited to simple text transformations. You can index mathematical operations, type casts, or even complex JSONB extractions:
-- Indexing a JSONB field for performance
CREATE INDEX idx_user_metadata_theme ON user_settings ((metadata->>'theme'));
Critical Consideration: The Planner
For the index to be used, your query must use the exact same function signature as the index definition. If you create an index with LOWER(email) but query with UPPER(email), the planner will ignore the index. Additionally, since the index must be recalculated whenever the base row is updated, avoid indexing extremely heavy functions, as this will impose a performance penalty on INSERT and UPDATE operations.
Key Takeaway: Functional indexes allow you to bypass expensive runtime calculations by pre-computing and storing the results of expressions within an index structure, enabling rapid lookups on non-columnar data.