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:

CREATE INDEX index_name    

ON table_name (p, q,r,….);

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:

WHERE p = value1 and q = value2 and r = value3;  

Or

WHERE p = value1 and q = value2;  

Or

WHERE p = value1;  

However, in the following cases, we will not use the index:

WHERE r = value3;  

Or

WHERE q = value2 and r = value3;  

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.

CREATE TABLE Person(  

    Person_id INT GENERATED BY DEFAULT AS IDENTITY,  

    first_name VARCHAR(30) NOT NULL,  

    last_name VARCHAR(30) NOT NULL  

);

Output

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.

INSERT INTO Person (First_name, Last_name)  

VALUES ('Mike', 'Ross'),  

('John','Smith),  

('Owen','Norman'),  

('Bruce','Willis'),  

('Douglas','Smith'),  

('Olivia', 'Smith');

Output

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:

SELECT *  

FROM Person  

WHERE last_name = 'Smith';

Output

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.

EXPLAIN  

SELECT *  

FROM Person  

WHERE last_name = 'Smith';

Output

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:

CREATE INDEX idex_person_names   

ON Person (last_name, first_name); 

Output

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:

EXPLAIN   

SELECT *  

FROM Person  

WHERE last_name = 'Smith'; 

Output

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:

SET enable_seqscan = off;  

Output

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:

SELECT *  

FROM Person  

WHERE last_name = 'Smith'  

AND first_name = 'John';

Output

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:

EXPLAIN  

SELECT *  

FROM Person  

WHERE last_name = 'Smith'  

AND first_name = 'John';

Output

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:

EXPLAIN  

SELECT *  

FROM Person  

WHERE first_name = 'John';

Output

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

• The PostgreSQL multicolumn GIN index can be used with query settings, including any subset of the index’s columns.
• Unlike B-tree or GiST, index search efficiency is similar and irrespective of index columns that the query conditions are using.

PostgreSQL multi-column index with GiST index

• The PostgreSQL multicolumn GiST index is also combined with query conditions, which contain any subset of the index’s columns.
• And the conditions on additional columns check the entries retrieved by the index.
• To define how much of the index wants to be scanned, the condition on the first column should be primary.
• If its first column contains only a limited distinct value, even though there are several different values in other columns, the GiST index will be comparatively unsuccessful.

PostgreSQL multi-column index with BRIN index

• The PostgreSQL multicolumn BRIN index can be used with query conditions, which contain any subcategory of the index’s columns.
• Alike GIN and unlike B-tree or GiST, index search efficiency is similar and irrespective of index columns that the query conditions are using.
• One of the most important purposes is to take multiple BRIN indexes rather than one multicolumn BRIN index on a single table is to have a different page_per_rangestorage constraint.

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.

Overview

• In the section, we have understood the PostgreSQL multicolumn index and the column order’s significance.
• The PostgreSQL Multicolumn indexes could be used carefully. In most cases, an index on a single column is enough and saves space and time.
• And we have also learned that the PostgreSQL multicolumn index can only support B-tree, GiST, GIN, and BRIN type of indexes.