0Pricing
SQL Academy · Lesson

B-tree vs Hash vs GiST vs GIN Indexes

Compare the main index types in PostgreSQL and pick the right one for equality, range, geometry, JSON, and full-text queries.

Index Types Overview

PostgreSQL has several index types, each optimised for different access patterns:

  • B-tree — equality and range (default)
  • Hash — equality only
  • GiST — geometric, full-text, custom
  • GIN — composite (arrays, JSONB, full-text)
  • BRIN — block range — huge, sorted tables
  • SP-GiST — space-partitioned trees

B-tree: The Default

Used 95% of the time. Supports =, <, <=, >, >=, BETWEEN, ORDER BY:

CREATE INDEX users_email_idx ON users(email);
CREATE INDEX orders_created_at_idx ON orders(created_at DESC);

All lessons in this course

  1. B-tree vs Hash vs GiST vs GIN Indexes
  2. Composite Indexes and Column Order
  3. Partial and Expression Indexes
  4. Index Maintenance and Bloat
← Back to SQL Academy