SQL Index Reference
Reference for database indexes: B-tree, hash, GiST, GIN, and indexing strategies.
Index Types
B-tree is the default, supporting equality, range, and sorting queries. Hash indexes support only equality lookups (faster but limited). GiST supports geometric and full-text search. GIN (Generalized Inverted Index) supports composite items (arrays, full-text, JSON). BRIN (Block Range Index) is compact for naturally ordered data (time-series). Partial indexes index only matching rows, saving space. Expression indexes index computed values. Covering indexes (INCLUDE clause) store extra columns to avoid table access.
- B-tree
- Default, range queries
- GIN
- Arrays, full-text, JSON
- partial
- Subset of rows
sortedinvertedWHERE conditionIndexing Strategy
Index columns used in WHERE, JOIN, and ORDER BY. Composite indexes follow the equality-range-sort rule: equality columns first, then range, then sort. Avoid over-indexing: indexes slow writes and consume space. Monitor index usage and drop unused indexes. Analyze query plans with EXPLAIN to verify index usage. Index-only scans require all columns in the index (covering index). Selectivity matters: indexing low-selectivity columns (boolean) rarely helps. Periodically rebuild/reindex to maintain performance (bloat).
- composite
- Multi-column
- covering
- INCLUDE extra cols
- selectivity
- High selectivity
equality-range-sortindex-only scanmore useful