Indexes serve as auxiliary structures within a database, serving two main purposes: enhancing data retrieval speed and enforcing integrity rules. Lets explore indexes in PostgreSQL in detail, look into the different types of indexes available, explain why there is such diversity, and see how they can be utilized to optimize query performance.

Index Types

As of version 9.6, PostgreSQL offers six built-in types of indexes. All index types associate a specific key with the corresponding table rows that contain that key. Each row is uniquely identified by a tuple id (TID), which comprises the block number within the file and the row's position within that block.

Any operation performed on indexed data, such as inserting, deleting, or updating table rows, necessitates updating the indexes for that particular table within the same transaction.

Extensibility of Indexes

PostgreSQL has implemented an interface in its general indexing engine to facilitate the easy addition of new access methods to the system. The primary purpose of this interface is to obtain tuple ids (TIDs) from the access method and perform some tasks. It reads data from the appropriate versions of table rows and retrieve row versions using individual TIDs or in batches using a prebuilt bitmap. It determines the visibility of row versions for the current transaction, considering its isolation level.

The indexing engine is involved in query execution and is invoked based on a plan generated during the optimization stage. The optimizer evaluates various ways to execute the query, taking into account the capabilities of all potential access methods.

By using the indexing engine, PostgreSQL ensures uniform handling of different access methods while considering their specific characteristics. The primary scanning techniques are index scan and bitmap scan. In an index scan, TID values are sequentially returned until the last matching row is reached, and the indexing engine accesses the table rows indicated by these TIDs. On the other hand, in a bitmap scan, all TIDs matching the condition are initially returned, and a bitmap of row versions is constructed based on these TIDs before reading the corresponding row versions from the table. The choice between these scanning techniques depends on factors such as the number of retrieved rows and is determined by the optimizer.