Mastering PostgreSQL 12 - Third Edition

GIN indexes

Generalized inverted (GIN) indexes are a good way to index text. Suppose you want to index 1 million text documents. A certain word may occur millions of times. In a normal B-tree, this would mean that the key is stored millions of times. This is not the case in a GIN. Each key (or word) is stored once and assigned to a document list. Keys are organized in a standard B-tree. Each entry will have a document list pointing to all the entries in the table that have the same key. A GIN index is very small and compact. However, it lacks an important feature that's found in B-trees—sorted data. In a GIN, the list of item pointers associated with a certain key is sorted by the position of the row in the table, and not by some arbitrary ...