PostgreSQL 101: Indexes and Index Types
PostgreSQL supports single-column, multicolumn, partial index, unique index, expression index, implicit index, and concurrent indexes. While these are general indexing methods, there are also index types. Index types supported by PostgreSQL B-tree, hash, GiST, Gin or BRIN‘Say.
PostgreSQL Index Types
The indexing type can be selected using the USING method during index creation. Different index types have different reasons for existence. For example, while B-tree indexes are efficient when queries contain a range and equality operator, hash indexes are used in the query if the equality operator is used.
B-tree Index
B-tree index is used when a query contains the equality operator (=) and range operators (<, , >=, BETWEEN, and IN).
Hash Index
Hash indexes are indexes that come into play only when there is an equality search operator in the query.
my_db=# EXPLAIN SELECT COUNT(*) FROM my_table WHERE item_id = 99;
QUERY PLAN
------------------------------------------------------------------ Aggregate (cost=8.02..8.03 rows=1 width=0)
-> Index Scan using item_hash_index on item (cost=0.00..8.02 rows=1 width=0)
Index Cond: (item_id = 99) (3 rows)GiST and GIN Index
GiST (Generalized Search Tree) indexes are the type you use when you want to create your own index method. Usually, since a custom data format is created, a custom method is also created to index it. GIN (Generalized Inverted Index) indexes are inverted indexes. They are used more frequently in search operations. These types of indexes keep an index entry for each word and, within this entry, a compressed list of the locations where the word appears. When searching with multiple words, these types of indexes first find the initial match and then perform the operation by using the index to filter out entries where the other words do not appear.
Both indexes are created more for, especially full-text searches. There is no obligation to create these indexes for full-text search; they are used depending on the situation, for performance, etc.
BRIN Index
BRIN (Block Range Index) indexes were introduced in PostgreSQL 9.5 and operate depending on where and how the table being created is stored, a feature still absent in many major database providers. BRIN can be used in very large tables when there is a natural correlation between the data in a specific column and where it is written on disk, and it can provide massive performance gains by skipping large blocks of data during full table scans.
BRIN reduces a large block of data into a compact summary and, by testing it against the summary at the very beginning of the query, excludes the large block from the query, thereby minimizing the large data to be scanned in detail. Otherwise, the very expensive “row-by-row full table scan” (full table scan) will be done.
BRIN indexes are used as a performance enhancer similar to horizontal partitioning or sharding. It would not be wrong to say that the BRIN index corresponds to the concept of “Storage Index” on the Oracle Exadata side.
