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.

Example:

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';

Output: