Query Index Types

title: Choose the Right Index Type for Your Data impact: HIGH impactDescription: 10-100x improvement with correct index type tags: indexes, btree, gin, gist, brin, hash, index-types

Choose the Right Index Type for Your Data

Different index types excel at different query patterns. The default B-tree isn’t always optimal.

Incorrect (B-tree for JSONB containment):

-- B-tree cannot optimize containment operators
create index products_attrs_idx on products (attributes);
select * from products where attributes @> '{"color": "red"}';
-- Full table scan - B-tree doesn't support @> operator

Correct (GIN for JSONB):

-- GIN supports @>, ?, ?&, ?| operators
create index products_attrs_idx on products using gin (attributes);
select * from products where attributes @> '{"color": "red"}';

Index type guide:

-- B-tree (default): =, <, >, BETWEEN, IN, IS NULL
create index users_created_idx on users (created_at);

-- GIN: arrays, JSONB, full-text search
create index posts_tags_idx on posts using gin (tags);

-- GiST: geometric data, range types, nearest-neighbor (KNN) queries
create index locations_idx on places using gist (location);

-- BRIN: large time-series tables (10-100x smaller)
create index events_time_idx on events using brin (created_at);

-- Hash: equality-only (slightly faster than B-tree for =)
create index sessions_token_idx on sessions using hash (token);

Reference: Index Types