What is a GiST index, and how does it differ from GIN?
GiST is a flexible tree framework used for ranges, geometry, nearest-neighbour search and exclusion constraints. GIN is faster for lookups of elements inside values, while GiST is smaller and cheaper to update but lossy for text.
Explain it simply
A B-tree sorts values on one line. Some data does not fit on a line: a map region, a time range, a bus route. GiST ("Generalized Search Tree") groups nearby or overlapping items into bounding boxes, nested like boxes within boxes. A search skips every box that cannot match.
GIN says "which rows contain this element"; GiST says "which rows overlap, contain or sit near this value".
Example
-- range overlap: no double bookings of a seminar hall
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
hall text,
during tstzrange,
EXCLUDE USING gist (hall WITH =, during WITH &&)
);
-- nearest neighbour (PostGIS or point type)
CREATE INDEX shops_loc ON shops USING gist (location);
SELECT name FROM shops ORDER BY location <-> point '(19.07, 72.87)' LIMIT 5;For text search, GIN is usually faster to query, GiST is smaller and quicker to update but needs rechecks.
What to say in the interview
"GiST is a general tree for overlap and distance queries: ranges, geometry, nearest neighbour, and exclusion constraints. GIN is an inverted index, better for containment of elements. I pick GiST for ranges and spatial data, GIN for mostly-read full-text and JSONB."
Common mistakes
- Using B-tree on a range column and wondering why overlap queries are slow.