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.

Practice

1. What is the main purpose of an expression index in PostgreSQL?

easy

A. To create a backup of the database

B. To store data in a compressed format

C. To speed up queries that filter or sort by a calculated expression

D. To enforce foreign key constraints

Solution

1. Step 1: Understand what expression indexes do

   Expression indexes are special indexes built on the result of an expression or function, not just a column.

2. Step 2: Identify their main use

   They help speed up queries that filter or sort using that expression, improving performance.

3. Final Answer:

   To speed up queries that filter or sort by a calculated expression -> Option C

4. Quick Check:

   Expression index purpose = speed up expression queries [OK]

Hint: Expression indexes speed up queries using expressions [OK]

Common Mistakes:

• Confusing expression indexes with data compression
• Thinking expression indexes create backups
• Mixing expression indexes with constraints

2. Which of the following is the correct syntax to create an expression index on the lowercased username column in PostgreSQL?

easy

A. CREATE INDEX idx_lower_username ON users (LOWER(username));

B. CREATE INDEX idx_lower_username ON users ((LOWER(username)));

C. CREATE INDEX idx_lower_username ON users [LOWER(username)];

D. CREATE INDEX idx_lower_username ON users {LOWER(username)};

Solution

1. Step 1: Recall expression index syntax

   Expression indexes require double parentheses around the expression inside the index definition.

2. Step 2: Check each option

   CREATE INDEX idx_lower_username ON users ((LOWER(username))); uses double parentheses correctly: ((LOWER(username))). Options B, C, and D use incorrect syntax.

3. Final Answer:

   CREATE INDEX idx_lower_username ON users ((LOWER(username))); -> Option B

4. Quick Check:

   Expression index syntax = double parentheses [OK]

Hint: Use double parentheses for expressions in CREATE INDEX [OK]

Common Mistakes:

• Using single parentheses instead of double
• Using square or curly brackets
• Missing parentheses around the expression

3. Given the table products(id INT, price NUMERIC) and the index:
CREATE INDEX idx_discounted_price ON products ((price * 0.9));
What will the query below use to speed up filtering?
SELECT * FROM products WHERE price * 0.9 < 100;

medium

A. It will perform a full table scan ignoring the index

B. It will use a default index on price if it exists

C. It will cause a syntax error due to the expression

D. It will use the expression index on (price * 0.9) to speed up the query

Solution

1. Step 1: Understand the expression index usage

   The index is created on the expression (price * 0.9), matching the WHERE clause expression exactly.

2. Step 2: Match query filter with index expression

   Since the query filters on price * 0.9 < 100, PostgreSQL can use the expression index to speed up filtering.

3. Final Answer:

   It will use the expression index on (price * 0.9) to speed up the query -> Option D

4. Quick Check:

   Matching expression in WHERE = index used [OK]

Hint: Expression index used if query expression matches exactly [OK]

Common Mistakes:

• Assuming default indexes are used instead
• Thinking expression indexes cause errors
• Believing full table scan always happens

4. You tried to create an expression index with:
CREATE INDEX idx_expr ON sales (price * discount);
But PostgreSQL returned a syntax error. What is the problem?

medium

A. Expression indexes require double parentheses around the expression

B. You cannot create indexes on expressions involving multiplication

C. The table name is missing

D. The index name is invalid

Solution

1. Step 1: Check expression index syntax

   Expression indexes must have the expression enclosed in double parentheses to be valid.

2. Step 2: Identify the syntax error cause

   The given statement uses single parentheses, causing a syntax error.

3. Final Answer:

   Expression indexes require double parentheses around the expression -> Option A

4. Quick Check:

   Double parentheses fix syntax error [OK]

Hint: Use double parentheses for expressions to avoid syntax errors [OK]

Common Mistakes:

• Using single parentheses for expressions
• Thinking multiplication is not allowed
• Ignoring syntax error details

5. You want to speed up queries filtering by the first 3 letters of a city column in a locations table. Which expression index will best help?
CREATE INDEX idx_city_prefix ON locations (???);

hard

A. LEFT(city, 3)

B. SUBSTRING(city FROM 1 FOR 3)

C. city[1:3]

D. city LIKE '___%'

Solution

1. Step 1: Identify correct expression syntax for substring

   PostgreSQL supports the function LEFT(string, n) to get the first n characters.

2. Step 2: Evaluate options for expression index

   LEFT(city, 3) uses LEFT(city, 3) correctly inside the index. SUBSTRING(city FROM 1 FOR 3) uses SUBSTRING but syntax is less common and may be less efficient. city[1:3] is invalid syntax. city LIKE '___%' is a condition, not an expression.

3. Final Answer:

   LEFT(city, 3) -> Option A

4. Quick Check:

   LEFT function best for prefix expression index [OK]

Hint: Use LEFT(column, n) for prefix expression indexes [OK]

Common Mistakes:

• Using invalid substring syntax
• Confusing LIKE pattern with expression
• Using array slice syntax on strings