PostgreSQL Create Index

In this section, we are going to understand the working of the PostgreSQL Create Index and the examples of the Create indexes command.

Why we use the PostgreSQL Create indexes command?

In PostgreSQL, the Create indexes command is used to create a new index by defining the index name and table or column name on which the index is created.

Syntax of PostgreSQL Create Indexes command

The syntax of creating an Indexes command is as follows:

CREATE INDEX index_name ON table_name [USING method]  

(  

    column_name [ASC | DESC] [NULLS {FIRST | LAST}],  

    ...  

);

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

Parameters	Description
Index_name	It is used to define the name of the index.And it should be written after the CREATE INDEXHere, we should try to give the easier and significant name of the index, which can be easily recalled.
Table_name	The table_name parameter is used to define the table name, which is linked with the Indexes.And it is specified after the ON keyword.
Using[method]	It is used to specify the index methods, such as B-tree, GIN, HASH, GiST, BRIN, and SP-GiST.By default, PostgreSQL uses B-tree Index.
Column_name	The column_name parameter is used to define the list if we have several columns stored in the index.The ASC and DESCare used to define the sort order. And by default, it is ASC.The NULLS FIRST or NULLS LASTis used to describe the nulls sort before or after non-null values.When DESCis defined, then the NULLS FIRST is considered as the default.And when DESC is not defined, then NULLS LAST is considered as default.

Note: We can use the EXPLAIN command if we want to identify whether a command uses an index or not.

Example of PostgreSQL Create Indexes

Let us see a sample example to understand the working of the PostgreSQL CREATE Indexes command.

But before discussing the example, we will see the index and telephone directory analogy as part of our example.

Index and Telephone directory analogy

For understanding the instance of index and telephone directory analogy, we have the following cases:

Case1: If the telephone directory is in alphabetical order.

• Suppose that we need to see the phone number of Mike Taylor in the telephone directory.
• As we also know that the names in the telephone directory are in alphabetical order.
• Therefore, firstly, we will check those pages where the last nameis Taylor, identify the first name is Mike, and finally retrieve his phone number.

Case2: If the telephone directory is not in alphabetical order.

• Assume that the names on the telephone directory were not in alphabetical order.
• Therefore, we should have to check all pages, like checking all names until we identify Mike Taylor’s phone number.
• And this process is known as a sequential scan that will go over all entries until we identify the one for which we are searching.

Note:

Parallelly to the telephone directory, the data stored in the table could be prepared in a specific order to accelerate several searches; hence, we have used the indexes.

An index can be considered as a data structure such as., B-Tree, which increases the rate of the data retrieval on a table at the price of further writes and storage to keep it.

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

And the Person_deatils table contains the various columns such as id, Person_name and Mobile_number, as shown in the below screenshot:

In the below command, we will try to identify the Person name whose Mobile_number is (444)-333-1234:

SELECT * FROM Person_details  

WHERE Mobile_number = '{(444)-333-1234}';

Output

On executing the above command, we will get the following output, where we successfully identify that person name whose mobile number is (444)-333-1234, which is Mike Taylor.

It is quite understandable that the database had to scan the complete Person_details table for identifying the Person_name as there is no index present for the Mobile_number column.

And here, we can use the EXPLAIN command to see the query plan, as we can see in the below command:

EXPLAIN SELECT *  

FROM Person_details  

WHERE Mobile_number = '{(444)-333-1234}';

Output

After implementing the above command, we will get the following output, which shows the Query plan of Person_details table.

Now for creating a new index, we take the values from the Moblie_phone column of the Person details table with the help of the below command:

CREATE INDEX idx_Person_details_Mobile   

ON Person_details(Mobile_number);

Output

On executing the above command, we will get the following message: the idx_Person_details_Mobile index has been created successfully.

If we want to identify the database engine, which contains the index for lookup, so we can use the above Explain command one more time, as shown in the following command:

EXPLAIN SELECT *  

FROM Person_details  

WHERE Mobile_number = '{(444)-333-1234}';

Output

We will get the following message on executing the above command, which shows the Query plan of Person_details table.

Overview

In PostgreSQL, we have Single-index column index, which helps us to enhance the performance of PostgreSQL Indexes.

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

• We have used the CREATE Index command to create a new index for the particular table.