api: YAML JSON TOON Database
version: 1.0.0
format: yaml
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:
    - Choose an index from a real filter and sort.
    - Explain why an added index did not help a query.
  caveats:
    - Planner choices depend on table size, statistics, and data distribution.
    - Extra indexes consume space and slow writes.
  related:
    - sql-window-functions
    - database-isolation-anomalies
  reviewed_at: "2026-10-12"
  sources:
    - title: PostgreSQL index types
      url: "https://www.postgresql.org/docs/current/indexes-types.html"
    - title: PostgreSQL multicolumn indexes
      url: "https://www.postgresql.org/docs/current/indexes-multicolumn.html"
    - title: PostgreSQL EXPLAIN
      url: "https://www.postgresql.org/docs/current/using-explain.html"
  reading:
    - 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:
    - section: data
      field: pattern
      type: string
      example: B-tree equality
    - section: data
      field: example
      type: string
      example: CREATE INDEX ON events (user_id)
    - section: data
      field: use
      type: string
      example: Look up rows by a frequently filtered value.
    - section: data
      field: tradeoff
      type: string
      example: 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: 1
  created_at: 1791798640
  updated_at: 1791798640
data:
  - pattern: B-tree equality
    example: CREATE INDEX ON events (user_id)
    use: Look up rows by a frequently filtered value.
    tradeoff: Low-selectivity filters may still favor a sequential scan.
  - pattern: Composite index
    example: CREATE INDEX ON events (user_id, created_at)
    use: Support a user filter followed by ordered time access.
    tradeoff: Column order should follow the real query shape.
  - pattern: Unique index
    example: CREATE UNIQUE INDEX ON users (email)
    use: Enforce a uniqueness rule with lookup support.
    tradeoff: Case and null semantics must match the domain.
  - pattern: Partial index
    example: CREATE INDEX ON jobs (created_at) WHERE state = 'pending'
    use: Index only the active subset of a large table.
    tradeoff: The query predicate must imply the index predicate.
  - pattern: Expression index
    example: CREATE INDEX ON users (lower(email))
    use: Index a repeated expression used by a query.
    tradeoff: The expression adds work to writes.
  - pattern: Covering index
    example: CREATE INDEX ON orders (customer_id) INCLUDE (total)
    use: Make selected payload columns available to index-only scans.
    tradeoff: Heap visibility can still require heap access.
  - pattern: BRIN
    example: CREATE INDEX ON logs USING brin (created_at)
    use: Summarize large physically correlated tables.
    tradeoff: Poor physical correlation reduces usefulness.
  - pattern: GIN array
    example: CREATE INDEX ON docs USING gin (tags)
    use: Support membership-style queries on supported values.
    tradeoff: Write cost and operator classes matter.
  - pattern: GiST range
    example: CREATE INDEX ON bookings USING gist (during)
    use: Support range overlap with a matching operator class.
    tradeoff: It is not a universal replacement for B-tree.
  - pattern: Measure the plan
    example: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
    use: Compare estimated and actual work.
    tradeoff: ANALYZE executes the statement; do not run unsafe writes casually.