Postgres indexes

An index is a separate data structure Postgres maintains alongside a table that makes specific lookups fast — pay storage and write-time cost to turn O(n) table scans into O(log n) lookups. The default is B-tree — handles =, <, >, BETWEEN, ORDER BY, and is what you want 90% of the time. The others exist for specific data shapes: Hash — only =, rarely worth it over B-tree; GIN — "inverted index" for arrays, JSONB, and full-text search; GiST — for geometric data (PostGIS) and range queries; BRIN — lightweight index for huge tables with naturally-ordered data (timestamps on append-only tables). Two things that trip people up: composite indexes (a, b, c) work for queries on a, a+b, a+b+c, but not b alone — order matters. And every index slows down INSERT/UPDATE/DELETE, so more indexes ≠ better. Always verify with EXPLAIN ANALYZE (see query planner).