Index Types¶

Single-column index: Index on one column. Best for equality (WHERE email = ?) and range (WHERE created_at > ?) queries on that column.

Composite (multi-column) index: Index on (a, b, c) sorted first by a, then b within equal a, then c. Useful for queries that filter on a leading prefix: WHERE a = ?, WHERE a = ? AND b = ?, WHERE a = ? AND b = ? AND c = ?. Cannot be used for WHERE b = ? alone — b is not the leading column, the index is not sorted by b globally. The rule: leftmost prefix. Column order matters: put the most selective (highest cardinality) column first when multiple queries use the index with different predicates.

Unique index: Enforces uniqueness. UNIQUE constraint automatically creates a unique index. The index serves dual purpose: lookup performance and constraint enforcement. Slightly more overhead on write than a regular index (must check for duplicates).

Partial index: CREATE INDEX idx ON orders (customer_id) WHERE status = 'pending'. Only pending orders are indexed — the index is smaller, writes to non-pending rows don't update it. Highly effective when a large fraction of rows are inactive and queries always filter by the partial predicate.

Covering index (index-only scan): CREATE INDEX idx ON orders (customer_id) INCLUDE (total, created_at). The INCLUDE columns are stored in the leaf nodes but not used for sorting. A query SELECT total, created_at FROM orders WHERE customer_id = ? can be answered entirely from the index — no heap fetch. Eliminates the most expensive part of index access (random I/O to heap pages).

Hash index: CREATE INDEX idx USING hash ON users (email). O(1) equality lookup, no range support. In PostgreSQL, B+Tree is usually preferred because it handles both equality and range, and hash indexes historically didn't support WAL (fixed in PG10 but B+Tree is still more common).

GIN (Generalized Inverted Index): Used for full-text search, JSONB columns, and arrays. GIN builds an inverted map: token → list of documents containing that token. WHERE doc_vector @@ query uses GIN to find all matching documents in O(log n + k). Write overhead is high — every new document requires updating the GIN for each token.

When NOT to index: Small tables (< 1000 rows): sequential scan is fast enough; index overhead (space, write cost) is not worth it. Low-cardinality columns (e.g., boolean, gender with 2 values): an index scan on 50% of rows is slower than a sequential scan. Write-heavy tables with many reads from analytics (not OLTP): index maintenance cost dominates.

Index fragmentation: Heavy DELETE workloads leave index pages partially empty. PostgreSQL does not immediately reclaim this space. A VACUUM marks dead tuples reclaimable but does not compact the index. REINDEX rebuilds the index from scratch — compacts it, removes all dead entries — but requires an ACCESS EXCLUSIVE lock (blocks reads+writes). In PostgreSQL 12+, REINDEX CONCURRENTLY rebuilds without locking.

Common pitfall¶

Creating a composite index (a, b, c) and then writing queries that filter on b and c but not a gets zero benefit from it — per the leftmost-prefix rule above, an index is only usable starting from its leading column. This is easy to introduce accidentally as an application evolves: the index was correct for the query patterns that existed when it was created, and nothing fails loudly when a new query pattern silently stops using it — EXPLAIN on the new query is the only way to notice, since a missing index doesn't error, it just falls back to a sequential scan.