How to Create Index If Not Exist in PostgreSQL

In this PostgreSQL tutorial, I will show how to create index if not exist in PostgreSQL.

You will learn about the syntax of creating an index and also you will learn how to create an index without using the IF NOT EXISTS clause. Then you will understand how to create an index using IF NOT EXISTS to check about already existing index on the table.

Additionally, I will tell you about the different index methods that you can use while creating an index and how to use the EXPLAIN ANALYZE command to view the execution time of the query etc.

How to Create Index If Not Exist in PostgreSQL

While managing data in the database, indexes play a crucial role. The index allows you to find the information quickly from the database.

• Let me give you an example, Indexes are like the table content of the book, Suppose you want to read a specific chapter from the book and to locate that chapter you will look into the table content of the book such as what is the page number of that chapter.
• 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, in this case, you will flip each page of the book to find that chapter which is very time-consuming.
• So now you understand how indexes are very useful, like that in PostgreSQL you can create an index for a table too and retrieve the information from the database very quickly.

The index also improves the performance of the queries, but the index also has some side effects like it takes extra space and sometimes makes things slow down.

But how index retrieves things in a faster way, basically index organizes things in a specific manner using the data structure such as B-tree, hashing etc, This data structure helps in finding, sorting and filtering information.

The syntax to create an index is given 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 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, and there are other methods such as gin, brin, spgist, gist etc.

You can use the below syntax to create an index on the table based on the given 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 makes things slow 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);

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 below code.

CREATE INDEX idx_emp ON employees (name);

After creating an index, whenever you run the query to find something, internally 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 of an E-commerce website and you have a task to create an index on the user’s table on column ‘name’ so that whenever any query regarding the user is done it should show the information quickly.

For example, here, I will create a dummy users table but you can relate this table to 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 table ‘users’, let’s insert 1 million random data using the below command.

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

Basically above statement will generate one million users which will start with User1, User2 and so on. The structure of the table 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';

In the above query, the EXPLAIN ANALYZE is a PostgreSQL command that shows how the database query planner executes the query and it shows the 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 human-readable format. It also shows information about the indexes etc.

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

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

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

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 same search for User2677 using the below query.

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

This time query planner uses the index idx_users as you can see in the above output, and the total execution time is 0.113 ms which is a huge difference from the earlier execution time of 77.075 ms.

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

Conclusion

In this PostgreSQL tutorial, you have learned how to create an index if not exist 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 command EXPLAIN ANALYZE.

You may also read:

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