Introduction to PostgreSQL index on expression

In PostgreSQL, indexes play an important role in optimizing query performance.

Typically, youcreate an index that references one or more columns of a table.

PostgreSQL also allows you to create an index based on an expression involving table columns. This type of index is called anindex on expression.

Note that the indexes on expressions are also known as functional indexes.

Here’s the basic syntax for creating an index on expression:

CREATE INDEX index_nameON table_name (expression);

In this statement:

• First, specify the index name in theCREATE INDEX clause.
• Then, form an expression that involves table columns of thetable_name in theON clause.

After defining an index expression, PostgreSQL will consider using that index when the expression appears in theWHERE clause or in theORDER BY clause of the SQL statement.

Note that maintaining indexes on expressions can incur additional costs. PostgreSQL evaluates the expression for each row duringinsertion orupdate and utilizes the result for building the index.

Therefore, it’s recommended to use the indexes on expressions when prioritizing retrieval speed over insertion and update speed.

PostgreSQL index on expression example

We’ll use thecustomer table from thesample database.

Thecustomer table has a b-tree index defined for thelast_name column.

First, retrieve the customers with the last names arePurdy:

SELECT    customer_id,    first_name,    last_nameFROM    customerWHERE    last_name= 'Purdy';

Output:

customer_id | first_name | last_name-------------+------------+-----------         333 | Andrew     | Purdy(1 row)

It returns one matching row.

Second, use theEXPLAIN statement to show the query plan:

EXPLAINSELECT    customer_id,    first_name,    last_nameFROM    customerWHERE    last_name = 'Purdy';

Output:

QUERY PLAN------------------------------------------------------------------------------- Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=17)   Index Cond: ((last_name)::text = 'Purdy'::text)(2 rows)

The output indicates that the query uses theidx_last_name index to improve the retrieval speed.

Third, find customers whose last name ispurdy in lowercase:

EXPLAINSELECT    customer_id,    first_name,    last_nameFROM    customerWHERE    LOWER(last_name) = 'purdy';

Output:

QUERY PLAN---------------------------------------------------------- Seq Scan on customer  (cost=0.00..17.98 rows=3 width=17)   Filter: (lower((last_name)::text) = 'purdy'::text)(2 rows)

However, this time PostgreSQL could not utilize the index for lookup. To enhance the speed of the query, you can define an index on expression.

Fourth, define an index on expression using theCREATE INDEX statement:

CREATE INDEX idx_ic_last_nameON customer(LOWER(last_name));

Finally, retrieve the customers based on a last name in lowercase:

EXPLAINSELECT    customer_id,    first_name,    last_nameFROM    customerWHERE    LOWER(last_name)= 'purdy';

Output:

QUERY PLAN------------------------------------------------------------------------------- Bitmap Heap Scan on customer  (cost=4.30..11.15rows=3 width=17)   Recheck Cond: (lower((last_name)::text) ='purdy'::text)   ->Bitmap Index Scan on idx_ic_last_name  (cost=0.00..4.30rows=3 width=0)         Index Cond: (lower((last_name)::text) ='purdy'::text)(4 rows)

This time PostgreSQL uses the index on the expressionidx_ic_last_name to quickly locate the matching rows in thecustomer table.

Summary

• Use the PostgreSQL index on expression to improve queries that have an expression involving table columns.

Last updated onFebruary 28, 2024

On this page

• Introduction to PostgreSQL index on expression
• PostgreSQL index on expression example
• Summary