How To Create An Index In PostgreSQL

In this PostgreSQL tutorial, I will show how to create index in PostgreSQL.You will learn about the syntax of creating an index with multiple approaches.

How To Create An Index In PostgreSQL

While managing data in the database, indexes play a crucial role. The index enables you to locate information in the database quickly.

• Let me give you an example: indexes are like the table of contents of a book. Suppose you want to read a specific chapter from the book, and to locate that chapter, you will look into the table of contents of the book, such as the page number on which that chapter is.
• As soon as you find the page number, you quickly jump to that chapter and start reading.
• Now think this way: a book without a table of contents means you will have to flip through each page to find the chapter, which is very time-consuming.
• Now you understand how indexes are handy. For instance, in PostgreSQL, you can create an index for a table and retrieve information from the database very quickly.

The index also improves query performance, but it has some side effects, such as occupying extra space and occasionally slowing down operations.

But how does an index retrieve things in a faster way? Essentially, an index organizes data in a specific manner using a data structure, such as a B-tree or a hash table. This data structure facilitates the retrieval, sorting, and filtering of information.

Basic Index Creation

Syntax

The syntax for creating an index is provided below.

CREATE INDEX indx_column ON table_name (column_name);

• CREATE INDEX: This is the command itself to create an index.
• indx_column: This is the name of the index that you are creating.
• On table_name: Used to specify the table on which the index is created.
• column_name: Name of the column on which the index is based.

By default, PostgreSQL uses the B-tree indexing method to create an index; other methods include GIN, BRIN, SP-GIST, and GIST.

You can use the syntax below to create an index on the table using the specified index method.

CREATE INDEX indx_column ON table_name (column_name) USING idx_method_name;

Using the above syntax, you can create an index, but if you use the same index statement, it may create multiple indexes for the same data, which is a waste of space and slows things down.

To resolve the above issue so that only one index is created for the same data, PostgreSQL introduces the new clause IF NOT EXISTS. When you execute the index statement with that clause, it only allows you to create an index if it doesn’t already exist.

The syntax is given below.

CREATE INDEX IF NOT EXISTS indx_column ON table_name (column_name);

Example

Let’s take a simple example where you will create an index. Suppose you have a table called employees, which is shown below.

Now, create an index named idx_emp using the code below.

CREATE INDEX idx_emp ON employees (name);

After executing the above query, I obtained the expected output, as shown in the screenshot below.

After creating an index, whenever you run the query to find something, it uses the index to find that information or data.

Let’s take a practical example where you will use the clause IF NOT EXISTS with the CREATE INDEX statement.

Suppose you manage a database for an E-commerce website, and you have a task to create an index on the user table’s ‘name’ column. This will enable queries regarding users to retrieve information quickly.

For example, I will create a dummy users table here, but you can relate this table to a real-world users table.

Let’s create a table ‘users’ using the below command.

CREATE TABLE users (
    id serial PRIMARY KEY,
    name varchar(255)
);

After creating the ‘users’ table, let’s insert 1 million random rows of data using the command below.

INSERT INTO users (name)
SELECT 'User' || generate_series(1, 1000000);

Essentially, the above statement will generate one million users, starting with User1, User2, and so on. The table structure is shown below.

Let’s search for the random user and see how it finds that user.

EXPLAIN ANALYZE SELECT * FROM users WHERE name ='User2677';

After executing the above query, I got the expected output as shown in the screenshot below.

In the above query, the EXPLAIN ANALYZE is a PostgreSQL command that shows how the database query planner executes the query, providing information about the actual query execution time.

Use the EXPLAIN ANALYZE command before the query to get the actual execution time of that query in a human-readable format. It also shows information about the indexes, etc.

As you can see when searching for User2677 in the table, it performs a sequential scan (Seq Scan) in parallel, and the execution time is 77.075 ms.

Now, create an index named ‘idx_users’ using the code below.

CREATE INDEX IF NOT EXISTS idx_users ON users(name);

After executing the above query, I got the expected output as shown in the screenshot below.

In the above query, use the IF NOT EXISTS with CREATE INDEX to check for the existing index of the same name on the table users.

Again, perform the exact search for User2677 using the below query.

EXPLAIN ANALYZE SELECT * FROM users WHERE name ='User2677';

This time, the query planner utilizes the index idx_users, as shown in the above output, and the total execution time is 0.113 ms, representing a significant improvement over the earlier execution time of 77.075 ms.

Remember, if you perform the query on smaller data, it may not utilize the created index; instead, it uses a sequential scan because it is more efficient for smaller datasets.

Creating Multi-Column (Composite) Indexes

For more complex query patterns, I often implement multi-column indexes.

CREATE INDEX idx_patients_state_city_zip 
ON patients (state, city, zip_code);

After executing the above query, I got the expected output as shown in the screenshot below.

Creating Unique Indexes

Unique indexes not only improve query performance but also enforce data uniqueness.

We can use the following query.

CREATE UNIQUE INDEX idx_customers_email 
ON customers (email);

After executing the above query, I got the expected output as shown in the screenshot below.

Conclusion

In this PostgreSQL tutorial, you have learned how to create an index in PostgreSQL with proper syntax and examples. You also learned how to view the actual execution time and which index is used to scan the table data using the EXPLAIN ANALYZE command.

Remember these key takeaways:

1. Start with indexes on primary keys, foreign keys, and frequently filtered columns
2. Monitor query performance and use EXPLAIN ANALYZE to identify indexing opportunities
3. Choose the right index type for your data and query patterns
4. Don’t over-index – each index adds overhead to write operations
5. Maintain your indexes regularly to ensure optimal performance

You may also read:

• How to Return a Record using PostgreSQL Function
• Format Number in PostgreSQL
• How to Rename a Column If Exists in PostgreSQL