Understanding Indexes in Postgresql

What is an index?

An index is a structure utilized for quick data retrieval operations. In the database world, an index is associated with a table and used to efficiently locate data without having to investigate every row in a database table. If a table does not have an index, then a full table scan is needed to find a record, which is very costly in terms of disk I/O and CPU utilization.
A full table scan is the process of sequentially reading every record from disk, checking against search criteria, and building a result set.

It is good to note that an index amends the performance of database operations at the cost of extra replicas of data. As an index stores the extra copy of data for more speedy access, an index is the routine way to amend the performance of the database.
In a nutshell, an index is a quick access path to a single row of a table in the database. A database index is similar to a book index where any specific information can be located by looking at the index page to avoid the full search of the book, which is an exhaustive operation. Similarly, a database index is created to minimize table traversal and maximize performance.
An index can be created utilizing a single column or multiple columns of a table. Once the index is created, then there is no further intervention needed; the index will be automatically updated on each DML operation on the table. After creating the index, it is the planner’s decision to utilize the index in lieu of sequential scans based on cost.

How to create an index

The CREATE INDEX command is used to create an index the basic syntax of creating an index on a table is as follows:

CREATE INDEX index_name ON table_name (column_name);

Here is an example of creating an index on the item_id column of the item table:

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

The result can be seen using the following statement:

warehouse_db=# \di item_idx;
               List of relations
 Schema |   Name   | Type  | Owner    | Table
--------+----------+-------+----------+-------
 public | item_idx | index | postgres | item
(1 row)

The index name is optional; if the index name is not specified, PostgreSQL generates the index name using the table name and the column name. In the preceding example, we have specified the index name. In the following example, PostgreSQL generates the index name using the table name and column name, which is item_item_id_idx:

warehouse_db=# CREATE INDEX ON item (item_id);

CREATE INDEX

The result can be seen using the following statement:

warehouse_db=# \di item_item_id_idx;
                 List of relations
 Schema |       Name       | Type  | Owner    | Table
--------+------------------+-------+----------+-------
 public | item_item_id_idx | index | postgres | item
(1 row)

Creating an index on a large table can take a long time; for example, in the preceding example, the query has to scan all the records and generate the index data. On a larger table, this process can take time.