Query Performance Quick Reference

A compact lookup for plan nodes, index selectivity, join algorithms, cost estimation, the profiling method, and the three most common anti-patterns.

Beginner

Before you use this reference

This page is a lookup, not a tutorial. The explanations live in Query Performance Fundamentals, Reading Query Execution Plans, Index Theory, Join Algorithms, Cost-Based Optimization, Systematic Query Profiling, and Query Performance Anti-Patterns.

Stages#

Text
Parse -> Plan (logical) -> Optimize (physical plan, cost-based) -> Execute

Reading a plan#

Text
Execution order: bottom-up (leaves first, root last) — read inside-out.Estimated vs. actual rows: the biggest gap = the most suspect estimate.Find the single dominant node by actual time before investigating anything else.
SQL
EXPLAIN ANALYZE SELECT ...   -- always ANALYZE, not just EXPLAIN

Indexes#

SituationIndex used?
High selectivity (order_id = 4021)Usually yes
Low selectivity (status = 'active', 50% of rows)Often no — sequential scan wins
Column wrapped in a function (UPPER(status) = ...)No — breaks index usage
Composite index, filter on a non-leading columnNo — leftmost-prefix rule

Join algorithms#

AlgorithmCost shapeBest when
Nested looprows(outer) × rows(inner), or × log with an indexSmall outer input, indexed inner
Hash joinrows(a) + rows(b)Large equi-join, no useful index, enough memory
Sort-mergeCheap if pre-sorted, else pays a sortBoth inputs already sorted (e.g. by index)

Cost-based optimization#

Text
Statistics (row counts, histograms) -> cardinality estimates                                     -> cost per candidate plan                                     -> cheapest estimate wins
Stale statistics = wrong estimates = a plan chosen for datathat no longer matches reality. Re-run ANALYZE after a large bulk load.

Profiling method#

  1. Baseline
    Measure the current time under realistic conditions.
  2. Plan
    EXPLAIN ANALYZE — find the one dominant node.
  3. Hypothesis
    One specific, testable reason for that node's cost.
  4. One change
    Exactly one — never several at once.
  5. Re-measure
    Same conditions, same cache state as the baseline.
  6. Keep or revert
    Then repeat on the next-largest node if still too slow.

Anti-patterns to check first#

Text
N+1 queries:              one query per row instead of one batched queryImplicit type conversion: comparing a string column to a numeric literalSELECT *:                 fetching columns and rows nothing downstream needsUnbounded result sets:    aggregating in application code instead of SQL

Further reading#

See also#

Query Performance Fundamentals
The four-stage model this whole module is organized around.
Query Performance Anti-Patterns
The three patterns worth checking before a longer profiling session.