SQL Indexing & Query Performance

Index Types — When to Use Each

B-tree (default): equality and range queries (=, <, >, BETWEEN, IN, IS NULL). Use for most columns.

Hash: equality only (=). Smaller and faster than B-tree for pure lookups, but cannot support range queries or ORDER BY.

GIN (Generalized Inverted Index): arrays, JSONB, full-text search. Fast reads, slow writes.

CREATE INDEX idx_events_meta ON events USING gin (metadata jsonb_path_ops);
SELECT * FROM events WHERE metadata @> '{"type": "click"}';

GiST (Generalized Search Tree): geometric data, range types, nearest-neighbor queries.