Skip to content
Tutorials Center
My learning

Topics

Lesson 41 of 90 ยท Indexes

GIN and GiST Indexes

Learn when to use GIN and GiST indexes in PostgreSQL: jsonb, arrays and full-text search with GIN, ranges, geometry and exclusion with GiST, plus pg_trgm.

The B-tree is built for one question: "where does this value sit in a sorted list?" That covers numbers, dates and strings, but plenty of data is not a single sortable value. A jsonb document holds many keys. An array holds many elements. A text document holds many words. A time range or a map region is not "less than" or "greater than" another one in any useful way. For these, PostgreSQL offers two general-purpose index frameworks: GIN and GiST.

You will not use them as often as B-trees, but when a query asks "which rows contain this?" or "which rows overlap this?", they are the difference between milliseconds and a full table scan. This lesson explains how each one works, which operators it supports, what it costs, and how the pg_trgm extension makes LIKE '%text%' searches fast. The index definitions are read-only in this track, since PostgreSQL's own types cannot run in your browser, but each idea has a live stand-in built with plain SQL that you can run and change. For the basics, see How Indexes Work in PostgreSQL.

GIN: an inverted index

GIN stands for Generalized Inverted Index. A B-tree maps each row to one key. An inverted index flips the relationship: it maps each element (a word, an array item, a JSON key) to the list of rows that contain it. It is the same structure a book's back-of-book index uses: "indexing: pages 12, 40, 87".

How GIN sees a row

Row A jsonb document, an array or a text value.
Split The type's operator class pulls out elements.
Posting lists Each element points to every row that has it.

To find rows containing X, look up X once and read its list.

1 / 2

You pick GIN by naming it with USING gin. The common uses are:

SQL
-- jsonb: containment and key-existence queries
CREATE INDEX products_attrs_gin ON products USING gin (attributes);

-- arrays: contains, is contained by, overlaps
CREATE INDEX articles_tags_gin ON articles USING gin (tags);

-- full-text search on a tsvector column
CREATE INDEX articles_search_gin ON articles USING gin (search_vector);

Each of those serves specific operators. A GIN index on a jsonb column accelerates @> (contains), ? (has key), ?|, ?& and the jsonpath operators @? and @@. On an array it serves @>, <@, && (overlaps) and =. On a tsvector it serves @@. For example:

SQL
-- Uses products_attrs_gin
SELECT id, name FROM products WHERE attributes @> '{"color": "red", "size": "M"}';

-- Uses articles_tags_gin
SELECT id, title FROM articles WHERE tags @> ARRAY['postgres', 'indexing'];

Note what GIN does not do: it does not help WHERE attributes ->> 'color' = 'red', because that extracts one value with an operator the index does not know. For such a query, either rewrite it as attributes @> '{"color": "red"}', or create a B-tree expression index on ((attributes ->> 'color')) (see Partial and Expression Indexes). GIN also cannot enforce uniqueness, cannot return rows in sorted order, and cannot do index-only scans.

Seeing the inverted index

SQLite has no GIN, but you can build the underlying data structure by hand. This stand-in unpacks a JSON array of tags into a table of (tag, doc_id) pairs, which is the logical content of a GIN index, and indexes it. Looking up one tag is then a single index search, and "contains all of these tags" becomes an intersection of lists:

SQL A hand-made inverted index (stand-in for GIN)

Press Run (or Ctrl + Enter) to see the result here.

The posting lists show sql pointing at documents 1, 2 and 3, and postgres at 3 and 4. The containment query returns Intro to indexes and Tuning Postgres, the two documents in both the sql and performance lists, and the plan is a SEARCH ... USING COVERING INDEX doc_tags_idx (tag=?). PostgreSQL's GIN does this work for you, on arrays, JSON and text, with compressed lists and much cleverer merging.

Choosing a jsonb operator class

A jsonb GIN index has two operator classes. The default, jsonb_ops, indexes every key and every value, and supports all the operators listed above. The alternative, jsonb_path_ops, indexes only hashes of whole paths-to-values and supports just @>, @? and @@:

SQL
CREATE INDEX products_attrs_path_gin
  ON products USING gin (attributes jsonb_path_ops);

It gives up key-existence (?) queries but in exchange is usually smaller and faster for containment. If your queries are all @>, prefer jsonb_path_ops.

The write cost: fastupdate

Inserting one document with 30 tags touches up to 30 places in a GIN index. To keep inserts quick, GIN by default buffers new entries in a pending list (the fastupdate option) and merges them into the main structure in bulk, when the list passes gin_pending_list_limit (4 MB by default) or when VACUUM runs. The trade-off: queries must also scan the pending list, and the unlucky insert that triggers the merge is slow. If you need predictable latency, create the index WITH (fastupdate = off). Either way, GIN suits data that is read far more often than it is written, and bulk loads are best done before creating the index.

GiST: a balanced tree for "nearness" and overlap

GiST (Generalized Search Tree) is a framework for tree indexes over data that has no single linear order. Where a B-tree stores sorted keys, a GiST index stores, for each branch, a summary region that contains everything beneath it: a bounding box for shapes, a covering range for ranges. A search descends only into branches whose summary could match, and prunes the rest. This is why GiST handles "overlaps" so naturally.

It is the index behind:

  • Range types: && (overlaps), @> (contains), <@, and the adjacent operators on tstzrange, int4range and friends (see Range and Multirange Types).
  • Geometric and PostGIS types: bounding-box overlap, containment and distance.
  • Nearest-neighbour search: ORDER BY location <-> point '(3,4)' LIMIT 5 returns the closest rows straight from the index.
  • Exclusion constraints: a rule such as "no two bookings of one room may overlap", which a B-tree unique index cannot express (see Exclusion Constraints and Temporal Design).
  • Full-text search, as a smaller, lossy alternative to GIN, and pg_trgm fuzzy matching.
SQL
CREATE INDEX bookings_period_gist ON bookings USING gist (period);

SELECT id FROM bookings WHERE period && tstzrange('2025-06-01 10:30', '2025-06-01 11:30');

-- Exclusion constraint: same room, overlapping times are impossible
ALTER TABLE bookings ADD CONSTRAINT no_double_booking
  EXCLUDE USING gist (room_id WITH =, period WITH &&);

That exclusion constraint needs the btree_gist extension, so that the plain room_id column can take part in a GiST index with the = operator.

Why a B-tree cannot do overlap

Overlap needs two conditions at once: the booking starts before the query window ends and ends after the query window starts. A B-tree on the start time can narrow only the first condition. The second must be checked on every candidate. Run it and read the plan:

SQL Why overlap queries need more than a B-tree (stand-in for GiST)

Press Run (or Ctrl + Enter) to see the result here.

The query finds bookings 2 and 4, and the plan reads SEARCH ... USING INDEX bookings_starts_idx (starts<?): the index narrows only by start time, and every booking that started earlier is read and then tested against ends. On a table with a million old bookings, that is a long list to filter. A GiST index on the tstzrange stores covering ranges per branch, so it can discard whole branches that cannot overlap the window.

pg_trgm: making LIKE '%text%' fast

A B-tree can serve LIKE 'abc%' (a fixed prefix) in some setups, but never LIKE '%abc%', because there is no sorted place to start when the text can appear anywhere. The pg_trgm extension solves it by indexing trigrams: every run of three characters in a string, with the string padded by two spaces in front and one behind. The word cat has trigrams " c", " ca", "cat" and "at ". A search for %cat% becomes "find rows whose trigram sets contain cat's", which is exactly what an inverted index does:

SQL
CREATE EXTENSION pg_trgm;

CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);

SELECT name FROM customers WHERE name ILIKE '%ngelo%';   -- uses the index
SELECT name FROM customers WHERE name % 'Jonh Smith';    -- fuzzy, similarity above a threshold

The same index serves LIKE, ILIKE, regular-expression matches (~) and the similarity operator %. You can create it with gist instead (gist_trgm_ops): GiST builds faster and is smaller, GIN is faster to search. The idea behind similarity is easy to reproduce. Here is a small version in SQL that generates trigrams with a recursive query and scores how alike three words are to postgres, as the number of shared trigrams divided by the number of distinct trigrams in both:

SQL Trigrams and similarity, computed by hand

Press Run (or Ctrl + Enter) to see the result here.

The misspelling postgress shares 8 of its trigrams with postgres (similarity about 0.727), progress shares 3 (0.2), and mysql shares none (0). That is the same measure pg_trgm's similarity() computes, and a trigram index can find the high scorers without comparing every row.

GIN or GiST?

Question GIN GiST
Typical data jsonb, arrays, tsvector, trigrams ranges, geometry, nearest-neighbour, trigrams
Exclusion constraints No Yes
Lookup speed Faster Slower, lossy (rows rechecked)
Build and update cost Higher Lower
Index size Often larger Often smaller

A short rule: documents and many-valued columns, GIN. Ranges, shapes, distance and exclusion rules, GiST. Both can be created concurrently, as described in Schema Changes and Migrations, and the next lesson covers the remaining types, BRIN, Hash and SP-GiST Indexes.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which query can use a default GIN index on a jsonb column?

    GIN serves containment (@>), key existence (?) and jsonpath operators. Extracting one value with ->> needs an expression B-tree index instead.
  2. 2. When is jsonb_path_ops a good choice instead of the default jsonb_ops?

    jsonb_path_ops indexes hashes of paths and values. It supports fewer operators (@>, @?, @@) but is usually smaller and faster for containment.
  3. 3. What does fastupdate do for a GIN index?

    The pending list avoids touching many index pages per insert. The cost is that queries also scan the list, and a merge occasionally stalls one insert. fastupdate = off gives steadier latency.
  4. 4. Which need can GiST meet but GIN cannot?

    Exclusion constraints and nearest-neighbour ordering are GiST features. GIN handles containment of many-valued columns but not these.
  5. 5. Why does LIKE '%abc%' need pg_trgm?

    With a leading wildcard the match can start anywhere. Trigram indexes break strings into three-character pieces so such searches can use an inverted index.

Summary

  • GIN is an inverted index: element to list of rows. Use it for jsonb, arrays, tsvector and trigrams (@>, ?, &&, @@).
  • Prefer jsonb_path_ops when you only run @>; use an expression B-tree for ->> equality.
  • GIN is read-optimized; fastupdate trades insert speed for pending-list scans, and fastupdate = off gives steady latency.
  • GiST stores summary regions in a tree: ranges, geometry, nearest-neighbour (<->) and exclusion constraints.
  • A B-tree can narrow only one side of an overlap test, which is why range overlap wants GiST.
  • pg_trgm indexes three-character pieces so LIKE '%x%', ILIKE, regex and similarity searches use an index.

Next: BRIN, Hash and SP-GiST Indexes, the remaining index types and a guide to choosing between them.