This topic describes Generalized Inverted Index (GIN) in PolarDB for Oracle.

GIN is an index that stores pairs of keys and posting lists. A key refers to a key value and a posting list is a set of positions where the key occurs. For example, the pair 'hello', '14:2 23:4' indicates that the key hello occurs at the positions of 14:2 and 23:4. These positions are tuple identifiers (TIDs), which are also known as row IDs. A row ID consists of a data block ID and an item point. The block ID is 32 bits in length, and the item point is 16 bits in length. GIN indexes allow you to find tuples that contain specified keywords. Therefore, GIN indexes are suitable for searching for multi-value elements.

Scenarios

  • Search for multi-value elements such as arrays and full-text indexes.Scenario 1
  • Search for data based on random columns.Scenario 2
  • Search for data that is sparsely distributed.Scenario 3

Operators

Operator Example
<@ select * from test where id <@ array[1,2];
@> select * from test where id @> array[1,2];
= select * from test where id = array[1,2];
&& select * from test where id && array[1,2];

You can also install the btree_gin plug-in to support B-tree operators.

Index structure

GIN index structure
  • Entry: an element in a GIN index.
  • Entry tree: a B-tree that is created based on the entry.
  • Posting list: a list of physical positions of entries.
  • Posting tree: a B-tree that is created based on the posting list.
  • Pending list: a list for temporary storage of index tuples. The pending list stores the fast updates.