PostgreSQL is a popular open-source relational database management system that provides multiple index types to optimize query performance and improve data retrieval efficiency. In this article, we will learn how to create different types of indexes in PostgreSQL. As much as possible, we applied indexes to the free 'DVD rental' sample database by using DML statements and Navicat for PostgreSQL 17.

1. B-Tree index:

B-tree index is the default index type in PostgreSQL, suitable for various data types including text, numbers, and timestamps. It uses a balanced tree structure to organize data, which facilitates efficient range queries and equivalent searches. Let's create a B-tree index on the "customer-id" column in the "payment" table:

CREATE INDEX btree_customer_id_idx ON payment(customer_id);

In Navicat, you will find indexes in the "Indexes" tab of the table designer. To create the above index, we need to enter "btree_custom_id_idx" in the "Name" field, select "customer-id" in the "Fields", and choose "B-Tree" from the drop-down list of "Index Method": s

The following is the btree_custom_id_idx index filled with all the fields mentioned above:

After clicking the 'Save' button, the index will be created.

2. Hash index:

Hash index is optimal for equality checking, but less effective for range queries. They use hash functions to map keys to index entries. Here's how to create a hash index on the "film_id" column of the "inventory" table, first using a DML statement:

CREATE INDEX hash_film_id_idx ON inventory USING HASH(film_id);

Now, the hash index created using Navicat is as follows:

3. GiST index:

The General Search Tree (GiST) index supports various data types and complex queries, making it highly suitable for applications such as full-text search and geometric data types.

This is an example of creating a GiST index on the geometry column:

CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);

4. SP GiST index:

SP GiST indexing is suitable for data types with multi-dimensional or hierarchical structures, and can efficiently index imbalanced trees.

This is an example of creating SP GiST indexes on tsvector columns:

CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);

 5. GIN index:

Universal Inverted Index (GIN) is an ideal choice for full-text search, group type, and composite data type. They are very effective for data types with multiple keys or components. Let's create a GIN index in the "title" column of the "film" table for full-text search:

CREATE INDEX gin_title_idx ON film USING gin(to_tsvector('english', title));

The gin_title_idx index has been added to the index tab of the "film" table in Navicat

6. BRIN index:

Block Range Index (BRIN) is suitable for large tables containing sorted data, as it indexes the range of data blocks rather than individual rows. It is very effective for columns with correlation between adjacent values. Here is how to create a BRIN index in the "rents_date" column of the "rental" table:

CREATE INDEX brin_rental_date_idx ON rental USING brin(rental_date);

The following is the index Brin_ rents_date_idx created in Navicat: