Choose the Right Index Type for Your Data

Why This Matters

Different index types excel at different query patterns. The default B-tree isn't always optimal. Using the wrong index type means your query might not use the index at all, falling back to a sequential scan.

The Problem

-- 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

The Solution

-- GIN supports @>, ?, ?&, ?| operators
CREATE INDEX products_attrs_idx ON products USING gin (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

Index Type Reference

Examples

-- 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);

-- 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);