PostgreSQL – Index On Expression
In this article, we will look into the process of leveraging the indexes on expression to optimize the query performance that has expressions.
Use the below syntax for creating an index on expression :
Syntax: CREATE INDEX index_name ON table_name (expression);
Let’s analyze the above syntax:
- After the CREATE INDEX clause mention the index name.
- Now create an expression that has table columns of the table_name.
For the purpose of demonstration, we will work with the customer table of the sample database, ie, dvdrental.
The customer table has a B-Tree index defined for the first_name column. The following query finds customers whose last name is “Purdy”:
SELECT customer_id, first_name, last_name FROM customer WHERE last_name = 'Purdy';
When executing this query, PostgreSQL uses the idx_last_name index as shown in the following EXPLAIN statement:
EXPLAIN SELECT customer_id, first_name, last_name FROM customer WHERE last_name = 'Purdy';
It will result in the following:
To improve this query, you can define an index expression like this:
CREATE INDEX idx_ic_last_name ON customer(LOWER(last_name));
Now, the query that finds customers based on the last name in a case-insensitive manner will use the index on expression as shown below:
EXPLAIN SELECT customer_id, first_name, last_name FROM customer WHERE LOWER(last_name) = 'purdy';