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.
Contact Us