PostgreSQL – Index Types

In this article, we will look into the index types in PostgreSQL and how to use them appropriately.

PostgreSQL has 6 primary index types:

  • B-tree indexes
  • Hash indexes
  • GIN indexes
  • BRIN
  • GiST Indexes
  • SP-GiST Indexes

Let’s discuss them in brief.

B-tree indexes

B-tree is a self-balancing tree that maintains sorted data and allows searches, insertions, deletions, and sequential access in logarithmic time.

PostgreSQL query planner will consider using a B-tree index whenever index columns are involved in a comparison that uses one of the following operators:



<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL

In addition, the query planner can use a B-tree index for queries that involve a pattern matching operator LIKE and ~ if the pattern is a constant and is anchor at the beginning of the pattern.

Example:

column_name LIKE 'foo%' 
column_name LIKE 'bar%' 
column_name  ~ '^foo'

Furthermore, the query planner will consider using B-tree indexes for ILIKE and ~* if the pattern starts with a non-alphabetic character which are the characters that are not affected by upper/lower case conversion.

Hash indexes

Hash indexes can handle only simple equality comparison (=). It means that whenever an indexed column is involved in a comparison using the equal(=) operator, the query planner will consider using a hash index.

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

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.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.