PostgreSQL Index types and use cases!

Indexes are database objects that can be created for a table to speed access to specific data rows by validating the existence of the data and pointing to the specific pages. Indexes store the values of the key(s) that were named when the index was created, and logical pointers to the data pages.

Although indexes speed data retrieval, they also can slow down data modifications (Each index creates extra work every time you insert, delete, or update a row) since most DML changes to the data also require updating the indexes.

Optimal indexing demands:

·       The workload on the table i.e. READs vs DMLs

·       An understanding of the behavior of queries that access unindexed heap tables, and tables with indexes

·       An understanding of the mix of queries that run on your server

·       An understanding of the PostgreSQL Query optimizer

Using indexes speeds optimizer access to data and reduces the amount of information read and processed from base tables. Whenever possible, the optimizer attempts index-seek-only retrieval to satisfy a query. With index-only retrieval, the database server uses only the data in the indexes to satisfy the query, and does not need to access rows in the table. The optimizer automatically chooses to use the indexes it determines will lead to the best performance.

Index Types

PostgreSQL provides several types of index: B-tree, Hash, GiST, SP-GiST, GIN and BRIN.

1. B-Tree

2. Hash

3. GiST

4. SP-GiST

5. GIN

6. BRIN

Each index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. The other index types are selected by the keyword USING followed by the index type name.

For example, to create an index:

CREATE INDEX name ON table USING [HASH|GIN](column);

 

Hash Indexes

Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the equal operator:

=

To create a hash index, you use the CREATE INDEX statement with the HASH index type in the USING clause as follows:

Syntax:

CREATE INDEX index_name ON table_name USING HASH (indexed_column);

GIN indexes

GIN stands for Generalized Inverted Indexes. It is commonly referred to as GIN.

GIN indexes are most useful when you have multiple values stored in a single column, for example, hstore, array, jsonb, and range types.

BRIN Indexes

BRIN stands for Block Range Indexes. BRIN is much smaller and less costly to maintain in comparison with a B-tree index.

BRIN allows the use of an index on a very large table that would previously be impractical using B-tree without horizontal partitioning. BRIN is often used on a column that has a linear sort order, for example, the created date column of the sales order table.

GiST Indexes

GiST stands for Generalized Search Tree. GiST indexes allow a building of general tree structures. GiST indexes are useful in indexing geometric data types and full-text search.

SP-GiST Indexes

SP-GiST stands for space-partitioned GiST. SP-GiST supports partitioned search trees that facilitate the development of a wide range of different non-balanced data structures. SP-GiST indexes are most useful for data that has a natural clustering element to it and is also not an equally balanced tree, for example, GIS, multimedia, phone routing, and IP routing.

 

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.