Indexes & Query Performance

EXPLAIN and Performance Methodology

Performance work is a controlled evidence loop: define the workload and correctness, capture a plan with estimates and safe runtime metrics, locate the first divergence or dominant work, change one hypothesis, and regression-test latency, resources, and writes.
  • EXPLAIN predicts; ANALYZE executes.
  • Plan trees flow bottom-up.
  • Estimated cost is not time.
  • Cache state is an experimental variable.
  • Data and parameters must be representative.
  • Regression controls include the write path.
Evidence-driven tuning loop
PostgreSQL read-only evidence capture
BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT JSON)
SELECT o.order_id, o.status,
       sum(i.quantity * i.unit_price) AS derived_total
FROM orders o
JOIN order_items i USING (order_id)
WHERE o.customer_id = :customer_id
  AND o.placed_at >= :from_time
GROUP BY o.order_id, o.status, o.placed_at
ORDER BY o.placed_at DESC
LIMIT 100;
ROLLBACK;
-- READ ONLY protects against accidental writes, not expensive reads or user-defined side effects.
Plan-reading checklist
EvidenceQuestion
Actual vs estimated rows × loopsWhere does the first large divergence begin?
Index Cond vs Filter / Rows RemovedWhich predicate navigates and which only rejects?
Buffers read/hit/dirtiedWhat page work occurred in this cache state?
Heap FetchesDid an index-only path still visit heap?
Sort Method / Disk; Hash BatchesDid bounded memory spill?
Planning vs execution timeIs compilation or execution the target?

Run multiple iterations and report distributions, not the fastest sample. Separate client/network time from server execution; control connection/session settings; refresh statistics; preserve both selective and hot parameter classes; and benchmark at production-like concurrency when contention matters.