Hash Index
Hash index can locate records very quickly when only equality comparison is involved in the searching on an index. The hash index entry is organized in key and value pairs, where the value point to the table record(s) contain(s) the key.
Given a key, it requires only one or two disk reads to retrieve the table record. It was quite a popular index algorithm in the days when memory was relatively expensive and using B-tree index caused more disk reads. However in Postgres, hash index operations are not WAL- logged, which means it has to be rebuilt manually after system failure.
Because of the difficulty in maintenance, it is not recommended of implementing Hash index in production system. B-tree index is considered as a good alternative for handling equity comparison.
GiST
B-tree index in Postgres can handle basic data type comparison like numbers and strings using balanced tree structure. In case of customized data type or advanced data type, using the B-tree index could not able to improve the performance. In order to optimize this kind of data types, Generalized Search Tree (GiST) is introduced in Postgres.
It is an extensible data structure, which a domain expert can implement for appropriate access method on customized data types or data types that go beyond the usual equality and range comparisons to utilize the tree-structured access method. GiST is also used in full text search by several of contrib modules.
In the next article, we'll dsicuss GIN indexes and Cluster operation
Top comments (0)