Query Optimization

Concept of the skill

Query optimization is the discipline of diagnosing and tuning one slow SQL query or one repeated query shape by reading the optimizer's evidence. The query planner takes a declarative SQL statement, applies rewrites, estimates cardinalities and costs from statistics and configuration, chooses an execution plan, and executes a tree of plan nodes. Tuning starts by asking: what query shape matters, what plan did the engine choose, what did the engine expect to happen, what actually happened, and what response follows from that mismatch?

The central evidence is not just "the query took 8 seconds." The evidence is the workload impact, exact SQL text, bind values, engine version, compatibility level, schema and indexes, row counts, statistics freshness, session settings, concurrency state, prepared-vs-literal execution path, plan tree, estimated vs actual rows, loops, buffers or reads, temp I/O, memory, WAL, JIT/planning timing, waits, and plan history. Modern engines also expose workload stores and adaptive features: PostgreSQL has pg_stat_statements, auto_explain, JSON/XML/YAML EXPLAIN, generic/custom plan controls, extended statistics, PostgreSQL 18 skip scan and asynchronous I/O, and PostgreSQL 19 beta plan-advice and IO visibility; SQL Server has Query Store, Query Store hints, automatic plan correction, PSP, OPPO, CE/DOP/memory grant feedback; MySQL exposes EXPLAIN ANALYZE, TREE/JSON plans, Performance Schema, optimizer trace, and histograms; Oracle exposes actual cursor plans through DBMS_XPLAN, AWR/SQL Monitor, SQL Plan Management, and SQL Analysis Report advice where available.

The response catalog is larger than "add an index." Possible responses include refreshing statistics, raising statistics targets, creating extended statistics or histograms, rewriting predicates to be sargable, changing CTE materialization, handling a generic/parameter-sensitive plan, replacing an N+1 pattern, changing join shape, tuning sort/hash memory carefully, disabling or retuning PostgreSQL JIT for short queries where compilation dominates execution, using an engine plan-management tool, adding or changing an index, changing the data model, precomputing, materializing, caching, fixing lock contention, or accepting the cost because the scan is correct.

Coverage

The discipline of diagnosing and tuning specific slow relational queries by reading actual plan evidence and matching the response to the root cause. Covers:

• Workload triage: prioritize by aggregate impact, such as calls times mean or percentile duration, total reads, temp I/O, and regression impact, not by the worst single execution alone.
• Safe plan capture: when to use EXPLAIN, EXPLAIN ANALYZE, actual execution plans, Query Store, pg_stat_statements, auto_explain, DBMS_XPLAN, SQL Monitor, MySQL EXPLAIN ANALYZE, Performance Schema, or slow logs; how to avoid side effects for DML.
• Plan-tree reading: estimates, actual rows, loops, inclusive vs local work, buffers/reads, temp I/O, memory, WAL, JIT/planning timing, rows removed, heap fetches, hash batches, sort spills, parallel workers, and settings.
• Root-cause taxonomy: cardinality misestimation, stale statistics, correlated predicates, parameter-sensitive plans, generic prepared plans, non-sargable predicates, wrong access path, wrong join method, sort/hash spill, JIT compilation overhead, materialization, N+1, locks/waits, plan regression, and too much work.
• Response selection: rewrite, ANALYZE/statistics target, extended statistics/histograms, index handoff, materialized view/precomputation, plan-management tool, memory/DOP or JIT tuning, isolation/lock investigation, schema handoff, application cache, or accept cost.
• Engine-specific adapters for PostgreSQL, MySQL, SQL Server, and Oracle without turning the skill into a vendor-only manual.