How deeply does Just Use Postgres! cover GIN index?

Explains extracted index entries, row references, operator classes, bitmap plans, and heap rechecks, but omits implementation-level maintenance internals.

GIN index

As explained in Just Use Postgres!

GIN is a PostgreSQL index access method designed for indexing composite values such as arrays and full-text search vectors.

A GIN (generalized inverted index) is a PostgreSQL index access method for composite values, especially JSON data, arrays, and full-text-search lexemes. Unlike the default B-tree, it stores searchable components separately and maps each component to table rows. Create one with the USING GIN clause. section 4.2 section 6.6.1

Use GIN when queries search inside a value rather than compare one scalar value:

Index a JSONB column with CREATE INDEX idx_pizza_orders_gin ON pizzeria.order_items USING GIN(pizza); The default operator class extracts JSON keys, values, and array elements as index entries. section 5.6.2
Search JSONB with containment or key-existence operators, such as pizza @> '{"type": "custom"}' or pizza ? 'special_instructions'. PostgreSQL can use the GIN index through a Bitmap Index Scan. section 5.6.2
Index a tsvector column for full-text search: CREATE INDEX idx_movie_lexemes_gin ON omdb.movies USING GIN (lexemes); Each unique lexeme references rows containing it. section 6.6.1

JSON containment and full-text search

A pizza query using pizza @> '{"sauce": "pesto"}' and pizza @> '{"size": "small"}' returns 35 rows through a Bitmap Index Scan and Bitmap Heap Scan. A movie query using lexemes @@ to_tsquery('historical & fight') returns three rows; after adding the GIN index, execution changes from a sequential scan taking 15.328 ms to a bitmap plan taking 0.150 ms. Nested JSON containment can require heap rechecks, as shown by 90 rows removed during recheck.

CREATE INDEX new_index_name ON tableA(columnA);

Compare the default CREATE INDEX form with the USING GIN form to see where the access method is selected. section 4.2

How Just Use Postgres! covers it

liveBook manning.com

Read Just Use Postgres! on liveBook Get the book at manning.com

Also explained in

liveBook manning.com

PostgreSQL Mistakes and How to Avoid ThemGIN is a PostgreSQL index access method designed for efficiently indexing composite values such as arrays and JSONB documents.

Compare how 2 books explain GIN index →

Related concepts

tsvectorused by · tsvector is a PostgreSQL data type representing normalized searchable lexemes extracted from text, optionally including their positions.
information retrievaluses · Information retrieval is the process of locating and selecting relevant information from an available collection in response to a query or information need.
bitmap index scanuses · A bitmap index scan is a query-plan operation that uses an index to build a bitmap of matching row locations before retrieving the corresponding table rows.
GiST indexalternative to · A GiST index is a PostgreSQL extensible index structure that supports generalized searches over diverse data types and predicates.
index typekind of · An index type is a database indexing method defined by the structure and access strategy it uses to support data retrieval.
indexingkind of · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.