Azpg index selection

Index selection on Azure Database for PostgreSQL Flexible Server

Pick the index that matches the query predicate and the data type, order multicolumn keys correctly, and build without taking a long lock. The wrong index wastes storage and write throughput while the query still seq-scans.

This skill is read-write. Diagnosing what exists and what's missing is safe and runs automatically; CREATE INDEX / DROP INDEX are guarded writes — present the DDL as a plan, get explicit confirmation, and build via scripts/create_index.sh (defaults to CONCURRENTLY, --dry-run capable) rather than free-handing index DDL on a live table. See Safety protocol.

Core index types are standard PostgreSQL. Vector (HNSW/DiskANN) specifics live in azpg-pgvector-rag; this skill covers when to reach for them. Deep per-type guidance is in reference.md; Flexible-Server build constraints (IOPS, maintenance_work_mem, locks) are in azure-constraints.md.

Live instance context (dynamic injection)

Ground recommendations in the real workload before proposing an index. PGCONN is a libpq connection string. These are read-only.

• Postgres major version (affects e.g. INCLUDE covering indexes, 11+): !psql "$PGCONN" -tAc "SHOW server_version;" 2>/dev/null || echo "(unknown — ask for connection details)"
• Biggest tables (index candidates live here): !psql "$PGCONN" -tAc "SELECT relname, n_live_tup, seq_scan, idx_scan FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;" 2>/dev/null || echo "(could not connect)"
• Unused indexes (0 scans since stats reset — candidates to drop, not add): !psql "$PGCONN" -tAc "SELECT indexrelid::regclass, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY 1 LIMIT 10;" 2>/dev/null || echo "(unknown)"

If injection is unavailable, run scripts/inspect_indexes.sql. Always confirm a query actually seq-scans (via azpg-explain-analyze) before adding an index — don't index on a hunch.

When to use this skill

Trigger on: "add an index for this query", "which index type — GIN or btree?", "why isn't my index used?", "this filter/JOIN/ORDER BY is slow", "index a JSONB / array / tsvector / range / geometry column", "find unused or duplicate indexes", "should this be a partial or covering index?". For reading the plan itself, pair with azpg-explain-analyze; for vector/ANN index tuning parameters, hand off to azpg-pgvector-rag.

Decision flow

Steps 1–2 are read/inspect (safe); steps 3–4 are write/mutate (guarded).

1. Confirm the need. EXPLAIN (ANALYZE, BUFFERS) the query; verify it seq-scans or mis-estimates. No seq scan on the hot path → maybe no index needed. (Use azpg-explain-analyze.)
2. Match type to predicate using the selection table and reference.md. Decide column order, and whether partial / covering / expression applies.
3. Build (guarded write) with scripts/create_index.sh — CONCURRENTLY by default to avoid a long write lock.
4. Verify the planner uses it (EXPLAIN again) and re-check that write amplification is acceptable; drop redundant indexes it supersedes.

Index type by access pattern

Predicate / data	Index	Notes
=, <, >, BETWEEN, ORDER BY, LIKE 'prefix%'	btree	the default; also serves range + sort + prefix
Equality only, no ordering	hash	crash-safe + logged since PG10; rarely beats btree
@>, ?, key/element existence on jsonb, arrays, tsvector	GIN	many-values-per-row; use jsonb_path_ops for smaller/faster containment-only
Geometry, ranges, nearest-neighbour, overlaps (&&)	GiST	PostGIS, tstzrange, exclusion constraints
Text pattern / evenly-partitionable data (e.g. LIKE '%x%' with trigram)	SP-GiST / GiST + pg_trgm	trigram index for substring search
Huge, naturally-ordered column (append-only created_at, ids)	BRIN	tiny; great when physical order tracks value order
High-dim vector similarity (<=>, <->, <#>)	HNSW / DiskANN	see azpg-pgvector-rag; DiskANN is Flexible-Server-only

Refinements that apply to any type:

• Composite order: put the columns used for equality first, then one range/sort column. (tenant_id, created_at) serves WHERE tenant_id = ? ORDER BY created_at.
• Partial index: add a WHERE to index only the rows you query (WHERE status = 'active') — smaller, cheaper to maintain.
• Covering index: INCLUDE (cols) lets an index-only scan skip the heap for those columns (PG11+).
• Expression index: index what you filter on (((data->>'email')), lower(email)) — the query must use the same expression.

Read / inspect steps (safe · auto)

• Index report — scripts/inspect_indexes.sql lists every index with size and scan count, flags unused (0 scans) and duplicate/overlapping indexes, and shows tables with high seq_scan relative to idx_scan (missing-index candidates).

• Confirm an index would/does apply — EXPLAIN and look for Index Scan / Index Only Scan / Bitmap Index Scan, not Seq Scan:

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM orders WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 20;
  

• Why is my index ignored? Common causes: predicate not sargable (function on the column, type mismatch), low selectivity (seq scan is cheaper), stale stats (ANALYZE), or the leftmost-column rule for composites.

Write / mutate steps (explicit · guarded)

Do not create or drop indexes without a plan and explicit confirmation. Index builds consume IOPS and (non-concurrent) take an ACCESS EXCLUSIVE-ish lock. Prefer scripts/create_index.sh — it defaults to CREATE INDEX CONCURRENTLY, can raise maintenance_work_mem for the build, and prints DDL under --dry-run.

-- Online build on a populated table (cannot run inside a transaction block).
CREATE INDEX CONCURRENTLY ix_orders_customer_placed
  ON orders (customer_id, placed_at DESC);

-- Partial + covering example.
CREATE INDEX CONCURRENTLY ix_orders_active
  ON orders (customer_id) INCLUDE (total)
  WHERE status = 'active';

-- JSONB containment.
CREATE INDEX CONCURRENTLY ix_docs_data
  ON docs USING gin (data jsonb_path_ops);

CONCURRENTLY builds without blocking reads/writes but takes longer, cannot run in a transaction, and can leave an INVALID index on failure — check with inspect_indexes.sql and DROP INDEX + rebuild if so. Drop superseded indexes after the new one is proven used, to reclaim write throughput.

Safety protocol

1. Confirm the query actually needs it (EXPLAIN shows a costly seq scan) — don't add speculative indexes; each one taxes every write.
2. Plan → confirm → execute. State the index DDL, its size estimate, the build method, and which existing indexes it may make redundant.
3. Use create_index.sh, CONCURRENTLY by default on populated tables. Note: concurrent builds can't be transactional and may leave an invalid index on failure.
4. Mind IOPS + memory. Large builds are I/O heavy; schedule off-peak and raise maintenance_work_mem for the session (see azure-constraints.md).
5. Re-verify and prune. After building, confirm the planner uses it and drop indexes it supersedes; keep the write path lean.

Bundled files

• reference.md — deep reference: per-type deep-dive (btree, hash, GIN/jsonb_path_ops, GiST, SP-GiST, BRIN, pg_trgm, ANN), composite ordering rules, partial/covering/expression patterns, "why isn't my index used" checklist, index maintenance (bloat, REINDEX CONCURRENTLY).
• azure-constraints.md — Flexible Server build limits: IOPS/storage, maintenance_work_mem, extension-backed index types on the allow-list, no pg_repack superuser paths.
• scripts/ — read-only inspect_indexes.sql; guarded create_index.sh (CONCURRENTLY default, --dry-run).
• examples/covering_index.md — worked example: slow query → chosen index → index-only scan, with before/after plans.