Index & Query Rules

Query and index standards prevent production regressions when ORMs generate SQL reviewers never see until p99 latency spikes.

Recipe

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.status, o.placed_at
FROM app.orders o
WHERE o.customer_id = $1
  AND o.status = 'open'
ORDER BY o.placed_at DESC
LIMIT 50;

Gate: No nested loop on million-row seq scan for API hot paths. Require index-friendly plan or documented exception in PR.

When to reach for this: API query review, BI access policies, and SME sign-off on ORM-generated SQL.

Working Example

-- API list endpoint backing query
CREATE INDEX idx_orders_customer_status_placed
  ON app.orders (customer_id, status, placed_at DESC)
  WHERE status IN ('open', 'pending');
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, placed_at
FROM app.orders
WHERE customer_id = 42
  AND status IN ('open', 'pending')
ORDER BY placed_at DESC
LIMIT 50;

Review checklist for PR:

• LIMIT or bounded date range present
• EXPLAIN uses index scan or bitmap index scan on large table
• No SELECT * on wide JSONB tables in hot path
• Join keys indexed on both sides for frequent joins
• pg_stat_statements mean time baseline recorded for changed query

Deep Dive

Rules at a Glance

API Query Anti-Patterns

-- BAD: unbounded
SELECT * FROM app.events WHERE tenant_id = $1;
 
-- GOOD: cursor or keyset pagination
SELECT id, created_at, type
FROM app.events
WHERE tenant_id = $1 AND created_at < $2
ORDER BY created_at DESC
LIMIT 100;

When Seq Scan Is OK

• Small tables (< few thousand rows) per EXPLAIN row estimate
• Analytical batch jobs off replica with explicit statement_timeout
• Post-migration before ANALYZE - temporary, not accepted for prod API

Index Discipline

-- One clear purpose per index
CREATE INDEX idx_events_tenant_created
  ON app.events (tenant_id, created_at DESC);

• Drop redundant indexes (tenant_id) when (tenant_id, created_at) exists
• Partial indexes for status-filtered queues

Gotchas

• ORM hides LIMIT - Default page size unbounded in admin views. Fix: Server-side max page size 100-500.
• LIKE '%foo' - Leading wildcard cannot use btree. Fix: Trigram GIN or full-text search.
• Wrong composite order - Index (status, customer_id) but query filters customer_id first. Fix: Match filter selectivity order.
• Stale stats after bulk load - Planner chooses seq scan. Fix: ANALYZE in migration pipeline.
• Prepared statement generic plan - PG 12+ may switch to suboptimal generic plan. Fix: Monitor after deploy; adjust plan_cache_mode if needed.

Alternatives

FAQs

Related

• RLS Performance - policy column indexes
• Postgres Project Rules Checklist - rules 6-10
• psql Best Practices - analyst habits

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.