#Locale

Examples use Indian conventions: ₹ amounts, IST, dd/mm/yyyy, Aadhaar and DPDP Act where a standard mentions identity or privacy law. Keep them when you copy an example.

#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:

sqlEXPLAIN (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:

Equality predicates first (tenant_id = ₹1, status = 'open')
Range predicate next (created_at > ₹2) — a range stops the index being usable for anything to its right
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

Type	Use
B-tree	Default; equality, ranges, sorting
Partial	A subset you query constantly — far smaller
Covering (`INCLUDE`)	Index-only scans; no heap fetch
Expression	When you filter on a function of a column
GIN	`jsonb`, arrays, full-text search
BRIN	Very large, naturally ordered tables (time series)
Unique	A correctness constraint that also indexes

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:

Cause	Fix
Function applied to the column	Expression index, or normalise on write
Type mismatch (`varchar` vs `int` parameter)	Cast correctly; align the column type
Leading `%` in a `LIKE` pattern	Trigram (`pg_trgm`) index or full-text search
Low selectivity — the query returns most rows	A scan genuinely is cheaper
Stale statistics	`ANALYZE` the table
`OR` across different columns	Rewrite as `UNION`, or index each branch
Small table	Sequential scan is faster; not a problem

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

Anti-pattern	Why it fails	Fix
One index per column	Composite queries still scan; writes slow	Composite in query order
Wrong composite order	Leftmost-prefix rule makes it unusable	Equality, range, sort
Unindexed foreign key	Parent deletes scan the child table	Index every FK
`WHERE lower(x) = ₹1` on an index of `x`	Function defeats the index	Expression index
`LIKE '%term%'` expecting B-tree	Leading wildcard cannot seek	`pg_trgm` or full-text
Indexing speculatively	Write cost with no read benefit	Index measured patterns
Never dropping unused indexes	Permanent write tax	Audit `idx_scan = 0`
`CREATE INDEX` without `CONCURRENTLY`	Blocks writes during the build	`CONCURRENTLY`
Assuming a `Seq Scan` is a bug	Often the correct plan	Read the row estimates
Reading `EXPLAIN` without `ANALYZE`	Estimates, not reality	`EXPLAIN (ANALYZE, BUFFERS)`

#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

Indexes