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:
In the above syntax, we have used the following parameters, as shown in the below table:
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.
Case2: If the telephone directory is not in alphabetical order.
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:
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:
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:
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:
We will get the following message on executing the above command, which shows the Query plan of Person_details table.
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: