Expression indexes in PostgreSQL

Introduction

Expression indexes help speed up searches on calculated or transformed data without storing extra columns.

When you want to quickly find rows based on a calculation or function result.

When you often filter or sort by a modified version of a column, like lowercased text.

When you want to avoid storing extra columns just for indexing purposes.

When you want to improve performance of queries using expressions in WHERE or ORDER BY.

When you want to index parts of data, like the first letter of a name.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name ((expression));

The expression is inside double parentheses to tell PostgreSQL it's an expression, not a column name.

You can use functions, calculations, or any valid SQL expression inside the index.

Examples

Creates an index on the lowercase version of the 'name' column to speed up case-insensitive searches.

PostgreSQL

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

Indexes the first letter of the 'name' column to speed up queries filtering by initial letter.

PostgreSQL

CREATE INDEX idx_first_letter ON products ((substring(name from 1 for 1)));

Indexes the price plus 10% tax calculation to speed up queries filtering by this value.

PostgreSQL

CREATE INDEX idx_price_plus_tax ON items ((price * 1.1));

Sample Program

This example creates a table with names, inserts some data, then creates an expression index on the lowercase fullname. The SELECT query uses lower(fullname) in WHERE, which uses the index for faster search.

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, fullname TEXT);

INSERT INTO employees (fullname) VALUES
('Alice Johnson'),
('Bob Smith'),
('alice cooper');

CREATE INDEX idx_lower_fullname ON employees ((lower(fullname)));

-- Query using the expression index
SELECT * FROM employees WHERE lower(fullname) = 'alice johnson';

Important Notes

Expression indexes only help if your query uses the exact same expression in WHERE or ORDER BY.

They do not store extra data, so they save space compared to adding computed columns.

Remember to analyze your queries to see if expression indexes improve performance.

Summary

Expression indexes speed up queries on calculated or transformed data.

They are created with CREATE INDEX using an expression inside double parentheses.

Use them when filtering or sorting by expressions like functions or calculations.