api: YAML JSON TOON Database
version: 1.0.0
format: toon
dataset:
  id: 1671
  slug: sql-index-patterns
  title: PostgreSQL Index Design Patterns
  description: "Index choices for equality, ordered retrieval, selective predicates, and specialized PostgreSQL workloads."
  summary: "Index choices for equality, ordered retrieval, selective predicates, and specialized PostgreSQL workloads. Each row includes a practical application and a failure to check before using it."
  use_when[2]: Choose an index from a real filter and sort.,Explain why an added index did not help a query.
  caveats[2]: "Planner choices depend on table size, statistics, and data distribution.",Extra indexes consume space and slow writes.
  related[2]: sql-window-functions,database-isolation-anomalies
  reviewed_at: 2026-10-12
  sources[3]{title,url}:
    PostgreSQL index types,"https://www.postgresql.org/docs/current/indexes-types.html"
    PostgreSQL multicolumn indexes,"https://www.postgresql.org/docs/current/indexes-multicolumn.html"
    PostgreSQL EXPLAIN,"https://www.postgresql.org/docs/current/using-explain.html"
  reading[1]: choose-sql-indexes-from-query-plans
  provenance: "Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses."
  entry_count: 10
  fields[4]{section,field,type,example}:
    data,pattern,string,B-tree equality
    data,example,string,CREATE INDEX ON events (user_id)
    data,use,string,Look up rows by a frequently filtered value.
    data,tradeoff,string,Low-selectivity filters may still favor a seque…
  category: Database Types
  category_slug: database-types
  tags: "database-types,sql index patterns,reference,engineering"
  view_count: 2
  created_at: 1791798640
  updated_at: 1791798640
data[10]{pattern,example,use,tradeoff}:
  B-tree equality,CREATE INDEX ON events (user_id),Look up rows by a frequently filtered value.,Low-selectivity filters may still favor a sequential scan.
  Composite index,"CREATE INDEX ON events (user_id, created_at)",Support a user filter followed by ordered time access.,Column order should follow the real query shape.
  Unique index,CREATE UNIQUE INDEX ON users (email),Enforce a uniqueness rule with lookup support.,Case and null semantics must match the domain.
  Partial index,CREATE INDEX ON jobs (created_at) WHERE state = 'pending',Index only the active subset of a large table.,The query predicate must imply the index predicate.
  Expression index,CREATE INDEX ON users (lower(email)),Index a repeated expression used by a query.,The expression adds work to writes.
  Covering index,CREATE INDEX ON orders (customer_id) INCLUDE (total),Make selected payload columns available to index-only scans.,Heap visibility can still require heap access.
  BRIN,CREATE INDEX ON logs USING brin (created_at),Summarize large physically correlated tables.,Poor physical correlation reduces usefulness.
  GIN array,CREATE INDEX ON docs USING gin (tags),Support membership-style queries on supported values.,Write cost and operator classes matter.
  GiST range,CREATE INDEX ON bookings USING gist (during),Support range overlap with a matching operator class.,It is not a universal replacement for B-tree.
  Measure the plan,"EXPLAIN (ANALYZE, BUFFERS) SELECT ...",Compare estimated and actual work.,ANALYZE executes the statement; do not run unsafe writes casually.