How deeply does PostgreSQL Mistakes and How to Avoid Them cover GIN index?

Explains index selection through SQL definitions, EXPLAIN plans, timings, and size comparisons, without covering GIN internals.

GIN index

As explained in PostgreSQL Mistakes and How to Avoid Them

GIN is a PostgreSQL index access method designed for efficiently indexing composite values such as arrays and JSONB documents.

A GIN (Generalized Inverted Index) is PostgreSQL’s practical choice for searching composite values such as JSONB documents and tsvector text. A common mistake is applying a B-tree expression index to document text: it can support equality or prefix matching, but not arbitrary substrings, and long values can exceed B-tree entry limits. section 4.4

Choose the index according to the predicate:

Use GIN with to_tsvector for token-oriented full-text queries using @@ and plainto_tsquery. section 4.4
Use GIN directly on JSONB for containment predicates such as data @> '{"doi": ...}'. section 4.4
Prefer a focused B-tree expression index for equality on one extracted JSONB field, such as data->>'doi'. section 4.4

Indexing article titles

CREATE INDEX ON test.arxiv USING gin (to_tsvector('english', data->>'title')); SELECT data->'doi' FROM test.arxiv WHERE to_tsvector('english', data->>'title') @@ plainto_tsquery('english', 'modeling of hydrogen'); EXPLAIN ANALYZE reports a Bitmap Index Scan and roughly 5 ms execution, replacing the multisecond sequential scan caused by '%modeling of hydrogen%'. section 4.4

DROP INDEX test.arxiv_lower_idx;
DROP INDEX

CREATE INDEX ON test.arxiv
    USING gin (to_tsvector('english', data->>'title'));
CREATE INDEX

Inspect the executable GIN definition for a JSONB-derived title search. section 4.4

How PostgreSQL Mistakes and How to Avoid Them covers it

Cover of PostgreSQL Mistakes and How to Avoid Them

liveBook manning.com

Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

liveBook manning.com

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

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 PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.