Query Optimization

Query Optimization

Query optimization is mostly about helping the planner make a good choice. That means better statistics, more selective predicates, and indexes that match the access pattern you actually need. The cleanest optimization is usually a better query shape, not a heroic hardware upgrade or a pile of hints.

Minimal Example

EXPLAIN ANALYZE
SELECT id, created_at, total
FROM orders
WHERE account_id = $1
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 100;

CREATE INDEX idx_orders_account_created_at
  ON orders (account_id, created_at DESC);

What It Solves

• Cuts response time and buffer churn by reducing the rows the engine must inspect.
• Keeps query cost predictable as tables grow and workloads shift.
• Makes the difference between a clean indexed plan and a full scan under pressure.

Failure Modes

1. Indexing every column independently while the real workload filters and sorts across combinations.
2. Selecting wide rows when a covering access path could satisfy the request.
3. Ignoring row-estimate errors from stale statistics or skewed distributions.

Production Checklist

• Start with EXPLAIN ANALYZE and compare estimated rows to actual rows.
• Align composite indexes to the filter and sort order of the real query.
• Measure before and after under realistic cardinality, not only on local sample data.

Closing

Optimizing queries is less about cleverness than about matching access paths to workload reality. The planner can only choose from what you give it.

---