iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

GIN / GiST Indexes

By · Reviewed & last updated · Part of PostgreSQL Tutorial

GIN and GiST are general inverted/tree index types built for data that does not fit a B-tree. GIN excels at multi-valued columns (arrays, jsonb, tsvector full-text); GiST excels at geometric and range queries. The rule of thumb: GIN for "contains" lookups on sets, GiST for "overlaps" or nearest-neighbour on shapes/ranges.

Index arrays, jsonb, tsvector, and a tsrange

EXAMPLE
-- 1) GIN on a tag array: 'all rows that contain BOTH ''sale'' AND ''featured'''
CREATE TABLE products (
  id          bigserial PRIMARY KEY,
  name        text NOT NULL,
  tags        text[] NOT NULL DEFAULT '{}',
  attributes  jsonb  NOT NULL DEFAULT '{}',
  search      tsvector GENERATED ALWAYS AS (
                  setweight(to_tsvector('english', name), 'A')
              ) STORED
);

CREATE INDEX products_tags_gin ON products USING gin (tags);
CREATE INDEX products_attrs_gin ON products USING gin (attributes jsonb_path_ops);
CREATE INDEX products_search_gin ON products USING gin (search);

-- Queries that USE these indexes:
SELECT id, name FROM products WHERE tags @> ARRAY['sale','featured'];
SELECT id FROM products WHERE attributes @> '{"color":"red"}';
SELECT id, ts_rank(search, q) AS rank
FROM products, plainto_tsquery('english', 'wool jacket') q
WHERE search @@ q ORDER BY rank DESC LIMIT 20;

-- 2) GiST for ranges — exclusion constraints to prevent overlaps
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id        bigserial PRIMARY KEY,
  room_id   bigint NOT NULL,
  period    tsrange NOT NULL,
  EXCLUDE USING gist (
    room_id WITH =,
    period  WITH &&         -- no two bookings in the same room can overlap
  )
);

CREATE INDEX bookings_room_period_gist
  ON bookings USING gist (room_id, period);

-- 3) Inspect index usage (catches "is the planner even using it?")
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM products WHERE tags @> ARRAY['sale'];

-- 4) See how big the index actually is
SELECT pg_size_pretty(pg_relation_size('products_tags_gin'));

Why it matters

jsonb_path_ops shrinks a jsonb GIN index by ~3x at the cost of only supporting the @> containment operator. If you only query with @> (the common case), use it. If you also do existence (?) or key-path queries, stick with the default ops class.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
CREATE INDEX idx_posts_body_fts ON posts
    USING GIN (to_tsvector('english', body));
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
Try it Yourself »

Discussion

Loading…