Query Performance Engineering (Advanced)

Theory

Advanced performance work is about closing the loop between:

• intent (what you want the query to do),
• reality (the execution plan),
• evidence (measured latency/throughput and resource usage),
• safe changes (indexes, rewrites, constraints).

1) Start with an evidence-first approach

Use EXPLAIN / EXPLAIN ANALYZE to answer:

• Is the plan using an index scan or doing full scans?
• Where are the largest costs (joins, sorts, aggregations)?
• How accurate are the optimizer’s row estimates?

2) Statistics and cardinality are first-class

Most “mysterious slow queries” come from:

• stale statistics,
• wrong assumptions about data distribution,
• skewed keys (some values occur far more frequently).

Fix by:

• updating table statistics,
• checking ANALYZE settings,
• considering partial/covering indexes for skew patterns.

3) Indexes for real workloads: covering vs filtering

A high-value index often does one of these:

• speeds up the WHERE clause (filtering)
• speeds up the JOIN key lookup
• speeds up ORDER BY / GROUP BY by aligning sort/group keys

“Covering” (index includes all columns needed) can reduce heap/table reads, but increases index maintenance cost.

4) Query rewrites: find the simplest equivalent plan

Common safe rewrites:

• replace nested subqueries with joins (when semantics match)
• materialize expensive intermediate results only when reused
• avoid functions on indexed columns in the predicate (non-sargable)

5) Concurrency & throughput: performance ≠ single-query speed

In production, overall throughput is limited by:

• lock contention
• buffer/cache pressure
• IO saturation
• connection pool sizing So measure under concurrency, not only single-run timing.

Code Example (SQL: diagnose + improve)

-- 1) Look at the plan (PostgreSQL style)
EXPLAIN ANALYZE
SELECT
  o.order_id,
  o.user_id,
  o.status
FROM orders o
WHERE o.user_id = 'c0a80123-0000-0000-0000-000000000001'
ORDER BY o.created_at DESC
LIMIT 50;

-- 2) If you see a Seq Scan, create a composite index that matches:
--    - filter on user_id
--    - order by created_at
CREATE INDEX IF NOT EXISTS orders_user_created_idx
ON orders (user_id, created_at DESC);

-- 3) Re-run EXPLAIN ANALYZE and compare costs/actual times.

Practice

1. Take one slow query you care about.
2. Capture:
   • EXPLAIN ANALYZE before any change
   • the plan shape (scan type + join/agg/sort costs)
3. Apply one change:
   • add a composite index OR rewrite a predicate OR update stats
4. Re-run and verify:
   • results are identical
   • latency improved and resource usage is acceptable

Common pitfalls

• Tuning based on intuition instead of plan evidence
• Adding indexes blindly without write/maintenance impact analysis
• Ignoring data skew and stale statistics
• “Optimizing away” correctness checks or edge-case behavior