PostgreSQL Index on Expression

In this section, we are going to understand the control of indexes on expression, which helps us to enhance the performance of the commands that also include the expressions. And also, see the example of PostgreSQL index on expression.

What is the PostgreSQL index on expression?

Generally, the create an index refers to one or various columns of a table. But we can also create an index based on an expression, which includes table columns, and these scenarios are known as an index on expression.

The functional-based indexes are called the indexes on expressions.

The Syntax of PostgreSQL index on expression

For creating an index on expression, we have the following illustration:

In the above syntax, we have used the following parameters, as shown in the below table:

Note:

• The maintenance of the indexes on expressions is quite expensive as the PostgreSQL has to assess each row's expression.
• Hence, If the retrieval speed is more critical than insertion and update speed, we can use the expressions' indexes.

Example of PostgreSQL Index on expression

Let us see a sample example to understand the working of the PostgreSQL Index on expression.

We are taking the Employee table, which we create in the earlier section of the PostgreSQL Tutorial.

And if we want to see the data present in the employee table, we will use the SELECT command, as shown in the below command:

Output

After executing the above command, we will get the following result, which contains the various columns such as emp_id, emp_fname, emp_lname, location, and the joining_date as shown in the below screenshot:

The Employee table has a B-Tree index defined for the emp_fname column. The below command is used to identify those employees whose emp_lname is Brown:

Output

After implementing the above command, we will get the following output, which displays the employee whose emp_lname is Brown.

Then, we are implementing the following command where the PostgreSQL uses the idx_emp_lname index using the EXPLAIN command:

Output

After executing the above command, we will get the following output: the PostgreSQL optimizer uses the Idx_emp_lname index for the employee table.

In the below command, we will try to identify those employees whose emp_lname is Brown in uppercase. But the PostgreSQL cannot use the index for lookup as shown below:

Output

We will get the following message on executing the above command, which shows the employee table's Query plan.

To enhance the above command, we can specify the index expression, as shown in the following command:

Output

The idex_ic_emp_lname index has been created successfully after we executed the above command.

We will now implement the below command, which helps us to identify the employee according to the emp_lname in a case-insensitive way, and uses the index on expression, as we can see in the following command:

Output

After executing the above command, we will get the following result:

Overview

In the PostgreSQL Index on expression section, we have learned the following topics:

• We have understood that the PostgreSQL index on expression is used to enhance the queries, which contain an expression that include the table columns.