Indexes
Expression indexes
Our customers table has an email column. To see some of the email addresses stored in it, run:
123SELECT emailFROM customersLIMIT 5;You should see:
12345Customer1@Example.comCustomer2@Example.comCustomer3@Example.comCustomer4@Example.comCustomer5@Example.comNotice that the email addresses contain uppercase letters.
PostgreSQL text comparisons are case-sensitive, so Customer1@Example.com is different from customer1@example.com.
Now imagine the application needs to find a customer by email regardless of how the user capitalizes the address. For example, the database contains Customer1@Example.com, but the user enters customer1@example.com.
One way to perform a case-insensitive lookup is to convert the stored email address to lowercase:
123SELECT *FROM customersWHERE lower(email) = 'customer1@example.com';Before creating an index, inspect how PostgreSQL executes the query:
1234EXPLAIN ANALYZESELECT *FROM customersWHERE lower(email) = 'customer1@example.com';You should see a sequential scan of the customers table.
The email column already has an index because it has a unique constraint. However, this query isn't searching for the original value stored in email. It is searching for the result of lower(email).
A regular index on email doesn't automatically provide an index on lower(email).
What is an expression index?
An expression index stores the result of an expression rather than the original column value.
For our query, we can create an index on lower(email):
12CREATE INDEX idx_customers_lower_emailON customers (lower(email));For each row in the customers table, PostgreSQL evaluates lower(email) and stores the resulting value in the index.
A simplified view looks like this:
For example, if a row contains Customer1@Example.com, the corresponding index entry contains customer1@example.com.
The important point is that the index stores the result of lower(email), not the original email value.
Using the expression index
Now run the query again:
1234EXPLAIN ANALYZESELECT *FROM customersWHERE lower(email) = 'customer1@example.com';The execution plan should now show that PostgreSQL used idx_customers_lower_email. For example:
1234Bitmap Heap Scan on customers Recheck Cond: (lower(email) = 'customer1@example.com'::text) -> Bitmap Index Scan on idx_customers_lower_email Index Cond: (lower(email) = 'customer1@example.com'::text)The Bitmap Index Scan on idx_customers_lower_email shows that PostgreSQL used the expression index to find matching rows instead of checking every row in the customers table.
Because the index stores the result of lower(email), PostgreSQL can search the indexed values for customer1@example.com and then retrieve the matching row from the table.
The query needs to use the indexed expression
The expression used by the query needs to correspond to the expression stored in the index.
Our index is defined on lower(email), so it can support queries such as:
123SELECT *FROM customersWHERE lower(email) = 'customer1@example.com';Now consider this query:
123SELECT *FROM customersWHERE upper(email) = 'CUSTOMER1@EXAMPLE.COM';This query uses upper(email), but our index stores the result of lower(email).
The idx_customers_lower_email index therefore doesn't provide the indexed expression values needed by this query.
Expression indexes are not limited to functions
An expression index doesn't have to use a function such as lower(). It can contain other expressions involving one or more columns.
For example, the order_items table contains quantity and unit_price. We could create an index on the result of multiplying these values:
12CREATE INDEX idx_order_items_totalON order_items ((quantity * unit_price));For each row, PostgreSQL would store the result of quantity * unit_price in the index.
The extra parentheses are required here because the indexed expression isn't written as a simple function call.
Expression indexes have a cost
Expression indexes can make queries that search using the indexed expression more efficient, but PostgreSQL has additional work to do when maintaining the index.
When a row is inserted, or when a value used by the expression is updated, PostgreSQL must calculate the expression and maintain the corresponding index entry.
For our index, if the value of email changes, PostgreSQL must calculate lower(email) and update the index.
As with other indexes, this additional work is one reason not to create expression indexes unless they support queries that the application actually needs.
Before continuing, remove the expression index:
1DROP INDEX idx_customers_lower_email;