What are the types of index access methods in PostgreSQL?

Introduction to access methods

An access method is an algorithm used to locate and access any data. Indexing plays a vital role in access methods. As an analogy, we can well imagine the difference in ease between looking for a particular term in a book by reading it sequentially, on the one hand, and finding it from an appendix section or a table of content, on the other. The latter is obviously much easier, and that’s exactly how indexing works.

Types of index access methods

PostgreSQL provides the following six different types of indexing used for different kinds of data:

1. B-Tree
2. Hash
3. GiST
4. SP-GiST
5. GIN
6. BRIN

The CREATE INDEX command creates B-Tree indexes by default. However, any indexing type can be selected with the USING clause.

CREATE INDEX index_name ON table_name USING HASH (column_name);

This query creates an index by using the hash method.

B-Tree (or balanced tree)

The B-Tree index is the most used index type. It is used by default in the CREATE INDEX query. It can handle all linearly-sortableData types that can be sorted in any order are called linearly-sortable data types. data types.

It supports equality and range queries using <, <=, =, >=, and > operators. The optimizer can use B-Tree indexes for pattern-matchingPattern matching allows us to search a specific pattern in the database using the LIKE clause in an SQL query. This type of SQL query finds a pattern instead of an exact match. queries as well.

Hash

The Hash index of PostgreSQL stores a 4-byte hash code of the indexed values. Since it only stores the hash of the value, there is no limit on the size of the indexed value. Any data type can be indexed using Hash.

It only supports simple equality comparison queries. A query plannerA query plan is a sequence of steps performed to access data in the database. A query planner is an algorithm that runs a query plan. uses a Hash index whenever a comparison query is performed on the indexed column using the = operator.

GiST (or generalized search tree)

The GiST index is a balanced, tree-structure access method that provides index support for multi-dimensional data like ranges or geometry point values. It has built-in operator classes for box, circle, multirange points, polygon, and other geometry shapes.

CREATE INDEX ON table_name USING GIST (column_name class_name);

GiST has the following built-in classes:

• box_ops
• circle_ops
• inet_ops
• multirange_ops
• point_ops
• poly_ops
• range_ops
• tsquery_ops
• tsvector_ops

GiST supports all geometric operations defined in the above built-in classes. It is optimized for nearest-neighborNearest neighbor query finds the nearest spatial objects to a specific spatial object. For example, a website store locator must find a store location that is nearest to the location of the customer. searches also.

The following query finds five closest places to the given point:

SELECT * FROM places ORDER BY location <-> point '(113,621)' LIMIT 5;

SP-GiST (or spaced partitioned gist)

The SP-GiST index allows indexing for non-balanced disk-based data structures, such as radix tree, k-d trees, and so on. It supports geometric operations of the following built-in classes.

• box_ops
• kd_point_ops
• network_ops
• poly_ops
• quad_point_ops
• range_ops
• test_ops

Due to distance ordering support, it provides ordering operators as well. Like GiST, SP-GiST is optimized for nearest-neighbor searches.

GIN (or generalized inverted index)

GIN indexes are suitable for multiple component values like arrays. Each composite must have a separate entry for its elements in the GIN index. Like GiST, GIN also allows developing custom data types.

It supports operators for the following built-in classes:

• array_ops
• jsonb_ops
• jsonb_path_ops
• tsvector_ops

BRIN (or block range indexes)

The BRIN index is designed to handle such tables that contain column values that have some natural correlation with their physical locations. For example, the timestamped column can have data placed in the same physical location.

The BRIN index stores a summary of the values located in the consecutive physical ranges of a table. It is the most helpful index type when values of a column are well-correlated with the physical order of the table rows. It has large built-in class libraries that support equality and range queries.