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:
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:
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:
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:
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:
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:
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:
After executing the above command, we will get the following result:
In the PostgreSQL Index on expression section, we have learned the following topics: