Indexes in PostgreSQL

In this article, we will walk through a detailed explanation of indexes, types, and ways indexes help in improving query performance.

A database index is a data structure used to organize data so that it is easier to retrieve data.

Database Indexes provide a solution for improving queries performance, but can also degrade performance at the same time if not used correctly. Keep following the article to know more details on indexes.

Type of Indexes

How do Indexes work?

Indexes consist of a set of tuples. The first tuple value is the search key, and the second contains a pointer(s) to a block on the hard drive where the entire row of data is stored. Based on the index type these tuples are then organized into different data structures.

Creating an index does not guarantee better database performance.
Every time we write to a table with an index, the database engine is updating both the table and any impacted indexes.

How we can decide which table column needs indexing?

1. A column that is more frequently queried with the “WHERE” clause, but not frequently changed.
2. A column has a referential integrity constraint.
3. A column has a UNIQUE KEY integrity constraint.

Syntax to create an index

CREATE index index_name on table_name using [method](col_name);

or

CREATE index index_name on table_name using [method](col_name [asc|desc] [nulls{first|last}],…..);

Syntax to drop an index

DROP index index_name;

Query to check existing indexes on table or database

Index on table

SELECT indexname,indexdef FROM pg_indexes WHERE tablename = ‘table_name’;