PostgreSQL Create Index

PostgreSQL indexes speed up SQL queries by speeding up lookups, filters and joins. Here’s how to create index in PostgreSQL to speed up database queries, using PostgreSQL CREATE INDEX statement.

How to Create Index in PostgreSQL

Here are the steps to create index in PostgreSQL using PostgreSQL CREATE INDEX statement. Let’s say you have the following table

CREATE TABLE orders(
   order_id INT PRIMARY KEY,
   product_id INT NOT NULL,
   sale INT NOT NULL,
   product_name VARCHAR(10)
);

How to Add Index to Column in Table

Let’s say you want to add index for column or set of columns, then use CREATE INDEX statement. Here’s the syntax of CREATE INDEX statement

CREATE [UNIQUE] INDEX [CONCURRENTLY] index_name ON table_name 
[USING METHOD](column_list)

In the above query, we specify the index name after CREATE INDEX, the table for which you need to create index, and list of columns to be included in indexing.

You can optionally specify UNIQUE keyword to ensure that your index contains only unique values

PostgreSQL supports various indexing methods such as btree, hash, gist, spgist, gin, and brin that you can specify during index creation.

By default, PostgreSQL uses btree indexes.

Here’s an example to add new index for column product_name in the above orders table

CREATE INDEX prod_id_index ON orders (product_id);

Here’s how to create index using multiple columns

CREATE INDEX prod_id_index ON orders (product_id, order_id);

Here’s how to create index using hash method

CREATE INDEX prod_id_index ON orders using hash(product_id, order_id);

Here’s how to create unique index to avoid duplicate values

CREATE UNIQUE INDEX prod_id_index ON orders (product_id);

PostgreSQL also allows you to create partial index using WHERE clause, where only matching values are indexed

CREATE INDEX prod_id_index ON orders (product_id) where status=1;

In the above query, only those prod_id are indexed where status=1

Hopefully, the above article will help you create index in PostgreSQL

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.