Databases store a huge amount of data daily for millions of companies across the globe which is then used to get useful information. PostgreSQL allows the user to query the data and find their information from the database created on it. It can take a lot of time to locate the required data from the pool of big data available in the database. To save time and energy by fetching data quickly, PostgreSQL allows the user to create indexes for tables and columns on a database.

This guide will explain how to create an index in PostgreSQL.

How to Create an INDEX in PostgreSQL?

Creating indexes allows the user to mark the location of a certain table or column which are used frequently in their queries. Using indexes, the database remembers the location of the column or table and fetches it every time the user runs the query. Without indexes, the query optimizer has to search from the whole table to find the exact match and load it for the user which can take time.

Syntax

The following is the syntax to create an index in the PostgreSQL table:

CREATE INDEX <index_name>
 ON <tab> <USING method>
   (
  column_name [ASC | DESC] [NULLS {FIRST | LAST }],
  ...
   );

Here the syntax contains:

- The CREATE INDEX command contains the name of the index to be created. It can be any valid name; however, it is a good practice to specify an easy-to-remember “index name”.
- Type the name of the table within the ON condition which should contain the index.
- Provide the index method such as “btree”, “gin”, “hash”, etc. with the table name and it uses “btree” as a default method.
- The index method should contain the name of the column on which the index should be applied and specify its order like ASC or DESC. It is optional to specify the order so if the user skips the order, Postgres uses ascending by default.

Example 1: Create an Index

Follow this example to find out how to create an index in the PostgreSQL table, start by accessing the employees table:

SELECT * FROM employees;

The employees' table does not contain any data for now but contains multiple columns:

The following command will be used to explain how the query optimizer applies the query on the table:

EXPLAIN SELECT * FROM employees
 WHERE department = 'Null';

It scans the complete table to find the department column containing a Null value:

Use the following query to create an index named “idx_department” on the department column of the “employees” table:

CREATE INDEX idx_department 
 ON employees (department);

Again check the explanation of the query by adding EXPLAIN before it:

EXPLAIN SELECT * FROM employees
 WHERE department = 'Null';

Now the query optimizer only scans the column using the index to find its referenced value:

Example 2: Create Multiple Indexes

The next example explains how to create multiple indexes by separating them using a comma as shown below:

CREATE INDEX idx_department_age 
 ON employees (department, age);

The query has created two indexes for the department and age column from the “employees” table:

Check both the indexes available in the “Indexes” section under the “employees” table:

That’s all about creating Indexes in the PostgreSQL table.

Conclusion

In PostgreSQL, the indexes are used to fetch data quickly and efficiently from a huge pool of data in a specific database. The user can create one index at a time or multiple indexes by separating them using commas. The query optimizer uses indexes to locate the exact match from the tables and without indexes it simply looks at the whole table to find the data. This guide has explained the Index in databases and its creation in the PostgreSQL databases.