PostgreSQL Multi-column Indexes
In this section, we are going to understand the creation of the PostgreSQL multicolumn Index, and these indexes are specified on more than one column of a table.
What is PostgreSQL Multi-column Indexes?
We can create an index on a various column of a table; such indexes are known as a multi-column index.
In other words, we can say that the multi-column indexes are those indexes, which are created with several columns of a table.
The multi-column indexes are also called composite index, concatenated index, and a combined index.
It can have a maximum of 32 columns of a table. And the limit can be altered by modifying the pg_config_manual.h while constructing PostgreSQL.
Furthermore, the multicolumn indexes only support the B-tree, GIST, GIN, and BRIN types of indexes.
The Syntax of PostgreSQL Multi-column indexes
The syntax of PostgreSQL Multi-column indexes is as follows:
Note: In the above syntax, p, q, r, are considered as a column name.
If we want to specify a multicolumn index, we should place the columns frequently which are used in the WHERE clause at the starting of the column list, and the columns which are used repeatedly in the condition after.
In the above illustration, we have the following scenarios where the PostgreSQL optimizer will consider using the index:
However, in the following cases, we will not use the index:
Example of PostgreSQL Multicolumn Index
To understand the working of the PostgreSQL multi-column index, we will see the following example.
So, we are creating one new table as Person with the CREATE command's help and inserting some values using the INSERT command.
To create a Person table into an Organization database, we use the CREATE command.
The Person table contains various columns such as Person_id, First_name, Last_name column, where we use the Person_id as the GENERATED ALWAYS AS IDENTITY constraint.
On executing the above command, we will get the following message, which displays that the Person table has been created successfully into the Organization database.
After creating the Person table successfully, we will enter some values into it with the INSERT command's help.
On executing the above command, we will get the following message window, which displays that the specified values have been inserted successfully into the Person table.
After creating and inserting the values in the Person table, we will identify those persons whose last_name is Smith, as shown in the following command:
After implementing the above command, we will get the following output, which displays all the persons whose last name belongs to Smith.
In the below command, we will use the EXPLAIN command to perform the Sequential scan on the Person table, which helps us to identify the equivalent rows as there was no index defined for the last_name column.
After successfully implementing the above command, we will get the below result, displaying that the PostgreSQL completed the sequential scan on the Person table.
Presently, the multicolumn indexes only support the B-tree, GiST, GIN, and BRIN index types.
PostgreSQL multi-column index with B-tree Index
In the below command, we are defining the B-tree index on both first_name and last_name columns.
Let us assume that we searching the person by the last name which is more frequent than searching by their first name.
Therefore, in the following command, we are specifying the index with the below column order:
After implementing the above command, we will get the following output, which displays that the Idex_person_names index has been created successfully for the Person table.
The PostgreSQL optimizer will use the index if we search for a person whose last name is Smith, as shown in the below command:
After executing the above command, we will get the following output:
In the above screenshot, we will see that the QUERY PLAN only uses the Sequential Scan, not the indexes, as we don't have enough data in our table for the planner to work with the index.
Hence, to see the index for the particular table, we have to use the following command:
After executing the above command, we will get the following message window: the particular command has been set successfully.
After executing the SET command, we will execute the EXPLAIN command once again, and we will get the following output, which displays the index in the Query plan:
In the next command, we are identifying those persons whose last name is Smith and the first name is John:
We will get the following message on executing the above command, which shows those persons whose first name is John and last name is Smith:
After that, the PostgreSQL Optimizer used the index for the above command as both columns (first_name and last_name) in the WHERE clause belong to the index:
After implementing the above command, we will get the below result: the PostgreSQL optimizer used the index for the first_name and last_name columns for the Person table.
But, if we search for the persons whose first name is John, then the PostgreSQL will perform a sequential scan of the table instead of using the index as shown in the following command:
After executing the above command, we will get the following output, which displays that the PostgreSQL optimizer performs the particular table's sequential scan.
Note: PostgreSQL could not force it even if the first_name column is related to the index.
PostgreSQL multi-column index with GIN index
PostgreSQL multi-column index with GiST index
PostgreSQL multi-column index with BRIN index
Note: When we specify a multicolumn index, we should always use the business environment to identify which columns are frequently used for lookup and used them at the starting of the column list while specifying the index.