⚡Query optimization

One change, one measurement

Rewrite for sargable predicates, shrink projections, push filters early, and batch work where semantics allow. Algebraic rewrites preserve answers while moving CPU and IO, always pair with EXPLAIN (ANALYZE, BUFFERS) (or your engine’s equivalent) on production-like cardinality.

SQL

Anti-pattern: wrapping indexed column

-- Bad for index on (order_date)
SELECT * FROM orders WHERE date_trunc('day', order_date) = DATE '2026-04-10';

✓Illustrative result set

plan
Seq Scan

SQL

Better: range on raw column

SELECT * FROM orders
WHERE order_date >= TIMESTAMP '2026-04-10'
  AND order_date < TIMESTAMP '2026-04-11';

✓Illustrative result set

plan
Index Scan on orders_order_date

SQL

Replace correlated subquery with join (sometimes faster)

SELECT c.customer_id, c.company_name, o.last_order
FROM customers c
JOIN LATERAL (
  SELECT max(order_date) AS last_order
  FROM orders o WHERE o.customer_id = c.customer_id
) o ON true;

✓Illustrative result set

customer_id	last_order
441	2026-04-08

Global knob tuning

Raising work_mem everywhere instead of fixing skew or bad join order.
Adding indexes without measuring write amplification on hot tables.
Optimizing rare admin queries while checkout path still fan-outs rows.

Target the bottleneck

Track total time = frequency × latency; focus on the hall-of-shame queries first.
Pair index experiments with rollback notes and replication lag checks.
Document winning patterns beside migrations so the next engineer knows why.

🧪Safety

Rewrites can change NULL and duplicate behavior.

Outer join reshapes, DISTINCT placement, and frame clauses all affect multiset results, add golden tests on fixtures with NULLs, ties, and empty groups whenever you ship a “performance-only” change.

Isolation

Sometimes the fix is workload routing, moving heavy analytics off the primary, rather than a cleverer SQL string on an already contended row store.

TL;DR

Query optimization means aligning SQL text, schema, and statistics so the engine picks cheap plans: selective indexes, correct join order (via hints only when necessary), fewer round trips, and bounded memory (avoid accidental sorts on huge sets). Measure with EXPLAIN ANALYZE and workload metrics, “faster SQL” without a cardinality story usually regresses later.