Expression indexes in PostgreSQL - Deep Dive

Overview - Expression indexes

What is it?

An expression index is a special kind of database index built not on a simple column, but on the result of a calculation or function applied to one or more columns. Instead of indexing raw data, it indexes the output of an expression, like a function call or arithmetic operation. This helps speed up queries that filter or sort based on those expressions. It is supported in PostgreSQL to optimize complex queries efficiently.

Why it matters

Without expression indexes, databases must compute expressions on every row during a query, which slows down performance especially on large tables. Expression indexes let the database precompute and store these results, making queries much faster. This means applications respond quicker and use less computing power, improving user experience and saving costs.

Where it fits

Before learning expression indexes, you should understand basic database indexes and how queries use them. After expression indexes, you can explore partial indexes and advanced indexing techniques like covering indexes or multicolumn indexes to further optimize queries.

Mental Model

Core Idea

An expression index stores the results of a calculation on table data so the database can quickly find rows matching that calculation without recomputing it each time.

Think of it like...

It's like having a phone book sorted not just by names, but by the first letter of the last name combined with the area code, so you can quickly find people based on that combined info without flipping through every page.

Table: users
Columns: id | first_name | last_name | email

Expression index on LOWER(email):

┌─────────────┐
│ Expression  │
│ Index Table │
├─────────────┤
│ lower(email)│ → points to rows in users
└─────────────┘

Query uses WHERE lower(email) = '[e-mail]' → uses index directly

Build-Up - 7 Steps

1

Foundation

What is a database index

Concept: Introduces the basic idea of an index as a tool to speed up data lookup.

A database index is like a book's index: it helps find data quickly without scanning every row. For example, an index on a 'name' column lets the database jump directly to rows with that name instead of checking all rows.

Result

Queries filtering by indexed columns run faster because the database uses the index to find matching rows quickly.

Understanding basic indexes is essential because expression indexes build on this idea by indexing computed values instead of raw columns.

2

Foundation

How queries use indexes

Concept: Explains how the database decides to use an index during query execution.

When you run a query with a WHERE condition on an indexed column, the database checks the index to find matching rows quickly. If no index exists, it scans the whole table, which is slower.

Result

Queries with indexed conditions are faster; without indexes, queries are slower due to full scans.

Knowing how queries use indexes helps you understand why indexing expressions can speed up queries that filter on computed values.

3

Intermediate

What is an expression index

Concept: Introduces the idea of indexing the result of a function or calculation on columns.

An expression index stores the output of a calculation on table columns. For example, indexing LOWER(email) stores lowercase emails in the index. Queries filtering with WHERE LOWER(email) can use this index to speed up searches.

Result

Queries using the expression in their WHERE clause run faster because the database uses the precomputed index.

Expression indexes let you optimize queries that filter on computed values, which normal indexes cannot speed up.

4

Intermediate

Creating expression indexes in PostgreSQL

🤔Before reading on: do you think you can create an index on a calculation like LOWER(column) directly? Commit to yes or no.

Concept: Shows the syntax and method to create expression indexes in PostgreSQL.

In PostgreSQL, you create an expression index using CREATE INDEX with the expression instead of a column name. Example: CREATE INDEX idx_lower_email ON users (LOWER(email)); This builds an index on the lowercase version of the email column.

Result

The database stores the lowercase emails in the index, speeding up queries filtering by LOWER(email).

Knowing the exact syntax empowers you to create indexes tailored to your query patterns, improving performance.

5

Intermediate

Using expression indexes in queries

🤔Before reading on: if you create an index on LOWER(email), will a query filtering on email (without LOWER) use it? Commit to yes or no.

Concept: Explains how queries must match the expression exactly to use the index.

For the database to use an expression index, the query's WHERE clause must use the same expression. For example, WHERE LOWER(email) = '[e-mail]' uses the index on LOWER(email). But WHERE email = '[e-mail]' does not use it.

Result

Queries matching the expression run faster; others do not benefit from the expression index.

Understanding this prevents wasted effort creating indexes that queries won't use.

6

Advanced

Combining expression and partial indexes

🤔Before reading on: can you create an index that only stores expression results for some rows? Commit to yes or no.

Concept: Shows how expression indexes can be combined with conditions to index only part of a table.

PostgreSQL allows partial indexes that index only rows meeting a condition. You can create an expression index with a WHERE clause. Example: CREATE INDEX idx_active_lower_email ON users (LOWER(email)) WHERE active = true; This index stores lowercase emails only for active users.

Result

Queries filtering on LOWER(email) and active = true use this smaller, faster index.

Combining expression and partial indexes optimizes performance and storage by focusing on relevant data.

7

Expert

Expression indexes and query planner behavior

🤔Before reading on: do you think PostgreSQL always uses expression indexes if they exist? Commit to yes or no.

Concept: Explores how PostgreSQL decides whether to use expression indexes and what can prevent their use.

PostgreSQL's query planner estimates costs and decides whether to use an expression index. Factors like statistics, expression complexity, and query structure affect this. Sometimes, the planner may skip the index if it thinks a sequential scan is cheaper. Also, if the query expression does not exactly match the index expression, the index won't be used.

Result

Expression indexes improve performance only when the planner chooses to use them, which depends on query and data characteristics.

Knowing planner behavior helps you write queries and indexes that the database will actually use, avoiding wasted optimization effort.

Under the Hood

When you create an expression index, PostgreSQL evaluates the expression for each row and stores the result in a special index structure (usually a B-tree). This index maps the computed values to the corresponding rows. During query execution, if the query filters on the same expression, PostgreSQL uses the index to quickly find matching rows without recalculating the expression for every row. The index is updated automatically when rows change.

Why designed this way?

Expression indexes were designed to optimize queries that filter or sort by computed values, which normal indexes cannot speed up. Instead of forcing users to store computed columns manually, expression indexes let the database handle this efficiently. This design balances flexibility and performance without duplicating data unnecessarily.

┌───────────────┐       ┌───────────────┐
│   Table Rows  │──────▶│ Expression    │
│ (id, email)   │       │ Calculation   │
└───────────────┘       │ (e.g. LOWER)  │
                        └──────┬────────┘
                               │
                               ▼
                      ┌─────────────────┐
                      │ Expression Index │
                      │ Stores computed  │
                      │ values + pointers│
                      └─────────────────┘

Query: WHERE LOWER(email) = '[e-mail]'
Uses expression index to find rows quickly.

Myth Busters - 4 Common Misconceptions

Quick: Does an expression index speed up queries filtering on the original column without the expression? Commit to yes or no.

Common Belief: An expression index on LOWER(email) will speed up queries filtering on email directly.

Reality: Expression indexes only speed up queries that use the exact expression (LOWER(email)), not the original column (email).

Why it matters: Assuming expression indexes help all queries on a column leads to wasted effort and no performance gain.

Quick: Do expression indexes store the original column data? Commit to yes or no.

Common Belief: Expression indexes store the original column values along with the computed results.

Reality: Expression indexes store only the computed expression results, not the original column data.

Why it matters: Misunderstanding this can cause confusion about index size and what data is indexed.

Quick: Will PostgreSQL always use an expression index if it exists? Commit to yes or no.

Common Belief: PostgreSQL always uses expression indexes if they exist for matching queries.

Reality: The query planner may choose not to use an expression index if it estimates a sequential scan is cheaper or if the query expression does not exactly match the index expression.

Why it matters: Expecting automatic use of expression indexes can lead to surprise poor performance and wasted optimization.

Quick: Can you create an expression index on any arbitrary expression? Commit to yes or no.

Common Belief: You can create expression indexes on any expression, including non-deterministic functions like random().

Reality: Expression indexes must be on immutable or stable expressions; non-deterministic functions are not allowed because index values must be consistent.

Why it matters: Trying to index non-deterministic expressions causes errors and confusion.

Expert Zone

1

Expression indexes can be combined with collations and operator classes to optimize locale-aware or case-insensitive searches beyond simple functions.

2

The planner's cost estimates for expression indexes depend heavily on up-to-date statistics; running ANALYZE frequently improves index usage decisions.

3

Expression indexes can be used to index JSON or array elements by applying functions extracting those parts, enabling efficient queries on complex data types.

When NOT to use

Avoid expression indexes when the expression is volatile or changes frequently, as index maintenance overhead outweighs benefits. For queries that filter on multiple columns without expressions, multicolumn indexes or partial indexes may be better. Also, if the expression is simple and used rarely, a sequential scan might be more efficient.

Production Patterns

In production, expression indexes are often used for case-insensitive searches (e.g., LOWER(text)), date/time transformations, or indexing computed flags. They are combined with partial indexes to focus on active or relevant rows. Monitoring query plans and index usage helps maintain performance as data and queries evolve.

Connections

Partial indexes

Builds-on

Understanding expression indexes helps grasp partial indexes because both optimize indexing by focusing on specific data subsets or computations.

Materialized views

Similar pattern

Both expression indexes and materialized views store precomputed results to speed up queries, but expression indexes integrate tightly with query filtering while materialized views store full query results.

Caching in computer systems

Same pattern

Expression indexes are like caches that store computed results to avoid repeated work, a principle common in many fields like CPU design and web performance.

Common Pitfalls

#1 Creating an expression index but querying without matching expression

Wrong approach:

CREATE INDEX idx_lower_email ON users (LOWER(email));
SELECT * FROM users WHERE email = '[e-mail]';

Correct approach:

CREATE INDEX idx_lower_email ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = '[e-mail]';

Root cause:

The query expression must exactly match the index expression for the index to be used.

#2 Using volatile functions in expression index

Wrong approach:

CREATE INDEX idx_random ON users ((random()));

Correct approach:

CREATE INDEX idx_lower_email ON users (LOWER(email));

Root cause:

Expression indexes require stable or immutable expressions; volatile functions like random() produce different results each time, so indexing them is invalid.

#3 Expecting expression index to always improve performance

Wrong approach:

CREATE INDEX idx_lower_email ON users (LOWER(email));
-- Query with different expression or planner chooses sequential scan
SELECT * FROM users WHERE LOWER(email) LIKE '%example.com';

Correct approach:

CREATE INDEX idx_lower_email ON users (LOWER(email));
-- Query matches index expression exactly
SELECT * FROM users WHERE LOWER(email) = '[e-mail]';

Root cause:

The planner may not use the index if the query expression differs or if the cost estimate favors a sequential scan.

Key Takeaways

Expression indexes store the results of calculations on table columns to speed up queries filtering on those calculations.

Queries must use the exact same expression as the index to benefit from expression indexes.

Expression indexes improve performance by avoiding repeated computation during query execution, but the query planner decides whether to use them.

Combining expression indexes with partial indexes can optimize performance further by focusing on relevant rows.

Expression indexes require stable or immutable expressions; volatile functions cannot be indexed this way.