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:
CREATE INDEX index_name
ON table_name (expression); In the above syntax, we have used the following parameters, as shown in the below table:
| Parameters | Description |
|---|---|
| Index_name | The index_name is used to define the index name, and it is written after the CREATE INDEX command. |
| table_name | It creates an expression that includes the table columns of a table name. |
| expression | When we specify the index expression, then the expression is specified either in the Order BY or WHERE clause in the SQL command. |
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:
Select * from employee; 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:
SELECT emp_id, emp_fname,emp_lname
FROM employee
WHERE emp_lname = '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:
EXPLAIN
SELECT emp_id, emp_fname,emp_lname
FROM employee
WHERE(emp_lname) = 'Brown';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:
EXPLAIN
SELECT emp_id, emp_fname,emp_lname
FROM employee
WHERE UPPER(emp_lname) ='Brown';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:
CREATE INDEX idex_ic_emp_lname
ON employee(UPPER(emp_lname));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:
EXPLAIN
SELECT emp_id, emp_fname,emp_lname
FROM employee
WHERE UPPER(emp_lname) ='Brown';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.
Leave a Reply