🧭Advanced indexing

Match access patterns, not column names

Beyond single-column B-trees: partial indexes shrink the indexed set, expression indexes make predicates sargable, INCLUDE columns build covering paths, and concurrent builds avoid long exclusive locks on large tables. Each lever changes which queries become cheap, and how much every INSERT pays.

SQL

Partial index (smaller, targeted)

CREATE INDEX idx_open_orders_customer
ON orders (customer_id)
WHERE status = 'OPEN';

✓Illustrative result set

result
CREATE INDEX

SQL

Expression index for lower(email)

CREATE UNIQUE INDEX uq_users_email_ci
ON users (lower(email));

✓Illustrative result set

result
CREATE INDEX

⚡ Advanced / dialect note

PostgreSQL CREATE INDEX CONCURRENTLY avoids long exclusive locks but takes longer and cannot run inside a transaction block, plan automation accordingly.

Spray-and-pray composite indexes

Indexing every column that appears in a WHERE without thinking about leading-column dominance and INCLUDE bloat, indexes become shadow tables and migrations get scary.

Order keys by selectivity and plans

Start from EXPLAIN on production-like volumes; validate insert/update p99 after aggressive covering indexes; keep partial predicates stable so the planner does not flip overnight.

📦Ownership

Treat hot indexes like versioned infrastructure.

Query class

Document which endpoint or report each index accelerates, expected QPS, and when you will drop it if the product pivots.

Statistics

After large data-shape changes, refresh histograms and re-check plans, optimizers lean on cardinality more than humans remember.

TL;DR

Advanced indexes (partial, expression, INCLUDE/covering) shrink scanned data or make predicates sargable so the optimizer can use a B-tree. Each index speeds specific reads but adds CPU and I/O on every write, validate with EXPLAIN (ANALYZE) and refresh statistics after bulk loads so cardinality estimates stay honest.