<!-- Generated from models/_canonical by scripts/build-model-variants.js. Edit the canonical source, not this file. Behavioural profile for Gemini: scripts/model-profiles.json -->

#Purpose

Rules for choosing indexes. Query rewriting is Database/query-optimization.

Two facts govern everything below: an index makes reads faster and writes slower, and an index the planner cannot use costs you everything and returns nothing. Index for measured query patterns, never speculatively.

---

#Index what you filter, join and sort on

Start from the actual queries, then read the plan:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 20;

Read for: Seq Scan on a large table, Rows Removed by Filter in the thousands, and a large gap between estimated and actual rows — that last one usually means stale statistics, so ANALYZE before concluding anything.

Always index:

• Foreign keys. PostgreSQL does not index them automatically, and an unindexed FK makes every parent DELETE scan the child table.
• Columns in WHERE on tables that grow.
• Join columns on both sides.
• ORDER BY columns where the sort would otherwise be external.

---

#Composite index column order

The single most consequential decision, and the one most often wrong.

sql
-- Serves: (tenant_id), (tenant_id, status), (tenant_id, status, created_at)
CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

The rule is leftmost prefix: an index on (a, b, c) can serve queries filtering on a, a+b, or a+b+c — never b alone, never c alone.

Order the columns:

1. Equality predicates first (tenant_id = $1, status = 'open')
2. Range predicate next (created_at > $2) — a range stops the index being usable for anything to its right
3. Sort column last, matching the ORDER BY direction

Put the most selective equality column first among equals. A composite index usually replaces a single-column index on its leading column, so drop the redundant one.

---

#Specialised index types

sql
-- Partial: index only the rows actually queried
CREATE INDEX idx_orders_open ON orders (tenant_id, created_at)
  WHERE status = 'open';

-- Covering: the query is answered from the index alone
CREATE INDEX idx_orders_lookup ON orders (tenant_id, id) INCLUDE (total, status);

-- Expression: without this, lower(email) cannot use an index on email
CREATE INDEX idx_users_email_lower ON users (lower(email));

Never wrap an indexed column in a function in the WHERE clause — WHERE lower(email) = $1 cannot use an index on email. Either index the expression or store the normalised value.

---

#When an index is ignored

The planner declines an index more often than people expect:

That fourth row matters: a sequential scan is not automatically a bug. For a query returning 40% of a table, a scan is the correct plan.

---

#The cost of too many

Every index must be updated on every INSERT, UPDATE and DELETE, and occupies memory that would otherwise cache data.

sql
-- Never used since the last statistics reset — candidates for removal
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

• Drop unused indexes. Verify across a full business cycle — a monthly report may be the only consumer.
• Drop redundant ones. (a) is redundant when (a, b) exists.
• Build and drop with CONCURRENTLY in production → Database/migration.
• Watch for duplicate indexes created by an ORM and a migration independently.

---

#Anti-patterns

---

#Checklist

• Verify: Indexes were added in response to a measured plan, not speculation
• Verify: Every foreign key is indexed
• Verify: Composite indexes order columns equality, then range, then sort
• Verify: Sort direction in the index matches the ORDER BY
• Verify: No WHERE clause wraps an indexed column in a function
• Verify: Partial indexes are used where queries target a consistent subset
• Verify: Covering indexes are used where an index-only scan is achievable
• Verify: jsonb, array and full-text columns use GIN
• Verify: Unused and redundant indexes are audited and dropped
• Verify: Index creation uses CONCURRENTLY in production
• Verify: ANALYZE has run before drawing conclusions from a plan

---

#Anchors (restated last, read last)

The rules that must hold when you stop, repeated here because the end of the context is what you act on:

• Never wrap an indexed column in a function in the WHERE clause — WHERE lower(email) = $1 cannot use an index on email. Either index the expression or store the normalised value.

• Indexes were added in response to a measured plan, not speculation

• Every foreign key is indexed

• Composite indexes order columns equality, then range, then sort

• Sort direction in the index matches the ORDER BY

• No WHERE clause wraps an indexed column in a function

• Partial indexes are used where queries target a consistent subset

Before reporting done, prove the module still imports — run the line for this stack and paste its output:

bash
python -c "import <package>"          # Python: the package you changed
node -e "require('./<entry>')"       # Node CJS, or: node --input-type=module -e "import './<entry>.js'"
go build ./...                        # Go