Expression indexes in PostgreSQL - Step-by-Step Execution

Concept Flow - Expression indexes

Write expression in index

↓

Create index on expression

↓

Query uses expression in WHERE

↓

PostgreSQL uses expression index

↓

Faster query execution

↓

END

Create an index on a computed expression to speed up queries that filter using that expression.

Execution Sample

PostgreSQL

CREATE INDEX idx_lower_name ON users ((lower(name)));

SELECT * FROM users WHERE lower(name) = 'alice';

Create an index on the lowercased 'name' column, then query filtering by lower(name).

Execution Table

Step	Action	Expression Evaluated	Index Used	Query Result
1	Create index on lower(name)	lower(name) for each row	Index idx_lower_name created	N/A
2	Run query with WHERE lower(name) = 'alice'	lower(name) computed for filter	Index idx_lower_name used	Rows with lower(name) = 'alice' returned
3	Return matching rows	N/A	N/A	User rows with name matching 'alice' case-insensitive
4	Query ends	N/A	N/A	Execution complete

💡 Query finishes after using expression index to quickly find matching rows.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
index idx_lower_name	Not created	Created on lower(name)	Used in query	Exists for future queries
query filter	N/A	N/A	lower(name) = 'alice'	Filter applied

Key Moments - 2 Insights

Why do we put parentheses around the expression in the index definition?

Parentheses indicate the index is on the expression result, not a column name. See execution_table step 1 where lower(name) is evaluated.

Will the index be used if the query filters on name = 'Alice' without lower()?

No, because the index is on lower(name), the query must use the same expression to use the index. See execution_table step 2.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the expression index created?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Check the 'Index Used' column in execution_table rows.

According to variable_tracker, what is the state of the index after Step 2?

A Used in query

B Created but not used

C Not created

D Dropped

💡 Hint

Look at 'index idx_lower_name' row after Step 2 in variable_tracker.

If the query used WHERE name = 'alice' instead of lower(name), what would happen?

A Expression index would still be used

B Query would fail

C Index would not be used

D Index would be rebuilt

💡 Hint

Refer to key_moments about expression matching in queries.

Concept Snapshot

Expression indexes in PostgreSQL:
CREATE INDEX idx_name ON table ((expression));
Use when queries filter by that expression.
Query must use same expression to use index.
Speeds up searches on computed values.

Full Transcript

Expression indexes let you create an index on a computed value, like lower(name). When you query filtering by that expression, PostgreSQL uses the index to find rows faster. The index is created by specifying the expression in parentheses. The query must use the exact same expression to benefit from the index. This speeds up searches on computed columns without storing extra data.