Types of Indexes in Postgresql

A PostgreSQL database supports the following index types:

• single-column index
• multicolumn index
• partial index
• unique index
• expression index
• implicit index and
• concurrent index.

The single-column index

The single-column index is utilized when a table represents mostly a single category of data, or queries span around only a single category in the table. Normally, in a database design, tables represent a single category of data, so generally a single-column (category) index is utilized.
Its syntax is as follows:

CREATE INDEX index_name ON table_name (column);

Let's consider the following example:

warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id = 100;

In this example, the rows are required where item_id is 100. If there is no index defined, then the whole table will be scanned to find the rows where item_id is 100, which is an expensive operation. If you look closely, only a single column is utilized in the WHERE clause, thereby creating an index on a single column, which is item_id in the case of the preceding query. This optimizes that query.
Now, consider the following example:

warehouse_db=# CREATE INDEX item_index ON item (item_id);

The result can be seen using the following statement:

warehouse_db=# \d item;

            Table "item"

Column	Type	Modifiers
item_id	integer
item_name	text
item_price	numeric
item_data	text

Indexes:

"item_index" btree (item_id)

Now, we have created a B-tree index, item_index, on a table item’s item_id column, so now we try the same SELECT query again and see how much time it takes after index creation.
Creating an index on a table reduces the SELECT query time drastically, as shown in the following output:

warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id = 100;

There is an obvious timing difference with and without the index. The same query without the index took significantly longer time to execute.