PostgreSQL

How Do I Index a Column in PostgreSQL?

A PostgreSQL database in particular or any other database, in general, can contain multiple tables in it. These tables consist of different columns or attributes against which different rows or records are stored. In this way, data is saved in a database. By indexing a column in PostgreSQL, we essentially mean to create a data structure through which we can reference the values of that column much more efficiently instead of going through all the values of that column manually. In this article, we will first discuss the need of indexing a column in PostgreSQL in Windows 10 followed by the method of doing so.

Need for Indexing a Column in PostgreSQL in Windows 10:

We have already mentioned that indexing a column makes the process of searching that column all the more speedy and efficient. However, apart from that, we will consider a very simple example over here that will justify the need of indexing a column in a table in PostgreSQL.

Suppose that we have a table titled “employee”. This table has two different columns namely “Name” and “Number” which correspond to the employee’s name and employee’s number respectively. Also, this table holds 1000 records of different employees. Now, we want to execute a query that will return a record with a specific employee’s number.

In this case, our query will have to look for the entire “Number” column of the “employee” table until it finds the specified number within the executed query. Only then, it will be able to display the desired record. This process is quite lengthy and time-consuming.

Therefore, we can try indexing the “Number” column so that instead of sequentially searching for the entire column, the specified number within the query can be searched very efficiently. However, this kind of column indexing is not suitable for very small tables i.e., tables with a few records because it will only increase the resource consumption.

Method of Indexing a Column in PostgreSQL in Windows 10:

If you wish to index any desired column of a table within your PostgreSQL database in Windows 10, then you will have to perform the step-wise procedure discussed below:

Step # 1: Launching the Windows 10 Command Prompt:

We will access the PostgreSQL environment through the Windows 10 command prompt for which we need to launch it first. You can see the following image for knowing how to launch the Windows 10 command prompt.

Step # 2: Entering the PostgreSQL Environment through Windows 10 Command Prompt:

After opening the command prompt, you can enter the PostgreSQL environment by running the command shown below:

> psql –U postgres

After executing this command, you will be asked to enter the password for the specified user as shown in the following image:

Once you will provide this password, you will enter the PostgreSQL environment through your Windows 10 command prompt.

Step # 3: Create a New Table in PostgreSQL in Windows 10:

Now, we will create a new table so that we can index one of its columns in Windows 10. A table in PostgreSQL can be created with the query shown below:

# CREATE TABLE employee (emp_ID serial PRIMARY KEY, emp_Name VARCHAR (255) NOT NULL, emp_Number VARCHAR (255) NOT NULL);

This query will create a table named “employee” in the current PostgreSQL database with three columns namely “emp_ID, emp_Name, and emp_Number” respectively.

Successful execution of this query will be confirmed once we will get the “CREATE TABLE” response on our console as shown in the following image:

Step # 4: Checking if the Newly Created Table Exists in your Current Database or not:

When our new table has been created, we can verify its existence by running the command shown below:

# \dt

This command will attempt to display all the tables that exist within the current database. In the list of these tables you will also be able to see our newly created “employee” table as shown in the following image:

Step # 5: Inserting Some Records into the Newly Created Table:

Now, we will insert some sample records into this newly created table. A record in this table can be added with the help of the query shown below:

# insert into employee values(1,’Aqsa’,’12345);

When this record will be added successfully to the “employee” table, you will see the following success message on your console:

In the very same manner, we will add multiple records to the “employee” table as shown in the image below:

Step # 6: Viewing the Newly Populated Table:

After populating our “employee” table, we can view it by executing the following appended query:

# select * from employee;

This query will display all the records of the “employee” table on the console as shown in the image below:

Step # 7: Executing a Test Query on the Newly Created Table:

Now, we will run a test query on the newly created table for displaying a record with a certain number. This query is as follows:

# select * from employee where emp_Number=24943’;

This query will instantly display the selected record as shown in the image below:

Step # 8: View the Query Plan for the Query You Have Just Executed:

Although the above-mentioned query has been executed successfully, however, for bringing the desired result to the console, the whole “emp_Number” column of the “employee” table would have been searched sequentially. You can check this by running the following query to display the query plan:

# explain select * from employee where emp_Number=24943’;

You can see from the image shown below that the specified query was executed by sequentially searching the “emp_Number” column of the “employee” table. The sequential searches are not good for tables with a large number of records. For resolving this problem, we will attempt to index the “emp_Number” column by performing the next step.

Step # 9: Creating an Index for a Column of the Created Table:

To create an index for a column of a table in PostgreSQL in Windows 10, you can run the following query:

# CREATE INDEX index_emp_Number ON employee(emp_Number);

This query will create an index named “index_emp_Number” for the column “emp_Number” of the table “employee”. Successful execution of this query will result in the success message shown below:

Step # 10: Listing All the Indexes of the Desired Table:

Now, for verifying if the said index has been created or not, you can run the following command:

# \d employee

The output displayed in the image shown below has highlighted the newly created index.

Removing an Index from a Column in PostgreSQL in Windows 10:

If you want to remove an index from a column of a table in PostgreSQL in Windows 10, then you can run the following query:

# DROP INDEX index_emp_Number;

When the specified index will be removed successfully, you will get the DROP INDEX response on the console as shown in the image below:

Conclusion:

By reading through all the steps of the method explained in this article, you will be able to understand very quickly how column indexing works in PostgreSQL in Windows 10. After learning that, you will be able to index as many columns of your tables in PostgreSQL as you want.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.