LLM Best Practices

B-tree Index

Last updated 2 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

A B-tree index keeps keys in sorted order in a balanced tree whose leaf pages point to heap rows. It serves =, ranges, ORDER BY (no sort node), and IS NULL on any sortable type. Default to it for columns in WHERE, JOIN, or ORDER BY, and use gin-index, GiST, or BRIN only when the query shape demands it (full-text, JSONB, arrays, geometry, huge append-only tables).

Rules

  • Order composite columns equality first, range or sort last: (user_id, created_at DESC). PostgreSQL 18 can skip-scan a multicolumn index when leading columns have few distinct values; do not rely on it over correct ordering.
  • LIKE 'prefix%' uses a B-tree only under the C locale or with a text_pattern_ops index; keep a default-opclass index too if you also need < or > comparisons.
  • Use partial indexes (WHERE status = 'pending') and covering indexes (INCLUDE, enabling Index Only Scans) to stay small.
  • Build on live tables with CREATE INDEX CONCURRENTLY; rebuild bloated ones with REINDEX CONCURRENTLY.
  • Every index slows writes (secondary-index).
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';
CREATE INDEX idx_orders_covering ON orders (user_id) INCLUDE (status, total_cents);

Backlinks