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!
