jacob-balslev 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.
Philosophy of the skill
Query optimization is mostly a reading discipline. The optimizer already tells you what it planned and, with actual execution instrumentation, what happened. A team that adds an index before reading the plan is guessing. A team that reads the plan may still add an index, but only after proving that the access path is the bottleneck and that the index is a durable response rather than a local patch.
The most common serious failure is still cardinality misestimation: the optimizer thinks a node will return 100 rows and it returns 10 million, or the reverse. Plans chosen for small row counts are bad for huge row counts, and vice versa. The right response is often ANALYZE, a higher statistics target, multivariate/extended statistics, MySQL histograms, SQL Server CE feedback, or a predicate rewrite. Adding an index without fixing the model may change nothing.
Modern optimizers are smarter than static rules. PostgreSQL 18 can use skip scan on some multicolumn B-tree indexes that older versions would ignore, and PostgreSQL 19 beta adds more optimizer and EXPLAIN visibility. SQL Server can maintain multiple plans for parameter-sensitive and optional-parameter cases. Oracle can preserve known-good plans and surface diagnostic advice. These features reduce some manual tuning, but they also make diagnosis more evidence-dependent: know the engine version, compatibility level, enabled features, and plan-management state before deciding what the plan means.
Evidence Capture Ladder
Use this ladder before proposing a fix:
- Rank the query by workload impact. Use
pg_stat_statements, SQL Server Query Store, MySQL Performance Schema / sys schema / slow query log, Oracle AWR/SQL Monitor, or application traces. Prioritize total time, tail latency, execution count, reads, temp I/O, and regression impact. - Capture the exact execution context. Record engine/version, compatibility level, schema and relevant indexes, row counts, statistics freshness, parameter values, prepared statement behavior, session settings, transaction isolation, concurrency conditions, and whether the slow case is production-only.
- Capture a safe actual plan. For pure
SELECT, prefer actual runtime plans. In PostgreSQL, useEXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, MEMORY, FORMAT JSON)when available; add PostgreSQL 19 betaIOonly when testing AIO behavior on a beta system. For DML, remember thatEXPLAIN ANALYZEexecutes the statement; use a transaction plus rollback, a safe replica, staging data, or plain EXPLAIN when side effects cannot be allowed. - Use structured output for tooling. Prefer JSON/XML/YAML/TREE plan output when an agent or script will inspect the plan, choosing the engine's structured explain format where available. Text-only plans are useful for humans but brittle for automation.
- Compare estimates to reality at every node. Large estimate/actual divergence is the first diagnostic branch.
- Read the tree, not just the biggest number. Parent node times and buffers can include child work; repeated inner nodes multiply by
loops; a top-level node is often expensive because its children are. A useful approximation for a node's total work is per-loop time times loops; local/exclusive work subtracts child totals, but prefer structured plan tools when precision matters. - Choose one response at a time and re-measure. Apply the smallest response that follows from the evidence, re-run the same representative plan, and check adjacent regressions.
- Hand off durable changes. Index portfolio changes go through indexing-strategy; schema/materialized-view changes go through entity-relationship-modeling and database-migration; isolation/lock correctness goes through transaction-isolation.
Quick Diagnostic Flow
Rank workload impact
-> Capture exact context
-> Capture a safe actual plan
-> Walk the plan tree
-> Compare estimates vs actuals
-> Inspect I/O, temp, memory, waits, JIT, and loops
-> Choose the smallest evidence-backed response
-> Re-measure the same representative case
-> Check adjacent regressions and hand off durable changes
The Diagnostic Procedure
- State the symptom in measurable terms. Include runtime, p95/p99 or mean, calls, total time, rows returned, rows scanned, reads/buffers, temp I/O, and whether the issue is new or long-standing.
- Get the actual plan for the representative case. Use the same parameter values, data distribution, session settings, engine version, compatibility level, and prepared/literal path the application uses.
- Walk the plan top-down and branch on estimates.
- Estimate much less than actual: the planner underestimated work. Refresh stats, raise stats target, add extended/multivariate stats, add histograms, check parameter sensitivity, check stale partitions, and inspect correlated predicates.
- Estimate much greater than actual: the planner overestimated work. Check stale stats, overly broad predicates, type/collation mismatch, missing constraints, and opportunities to expose selectivity.
- Estimate roughly equals actual but slow: the planner understood the work, and the chosen access path, join method, sort/hash memory, I/O, wait state, or amount of work is the issue.
- Inspect access paths. Confirm whether a scan is actually wrong. A sequential/table scan on a low-selectivity predicate or small table is often correct. An ignored index may be explained by low selectivity, function-wrapped columns, casts, collation/operator mismatch, partial-index predicate mismatch, generic prepared plans, version-specific skip-scan behavior, or stale stats.
- Inspect joins and loops. A nested loop is good for a small outer side and indexed inner lookup; it is bad when a large outer side causes millions of inner probes. A hash join is good for many equi-joins but can spill. A merge join needs sorted inputs and may be good when sort order already exists. A right anti join variant usually means the optimizer swapped sides for cost or memory; read it as an anti-join plan shape, not as a SQL rewrite rule.
- Inspect memory, temp, parallelism, waits, and compilation overhead. Sort or hash spills, high temp reads/writes, many hash batches, low or excessive DOP, poor memory grants, lock waits, and PostgreSQL JIT timing that dominates short-query execution are separate root causes from missing indexes.
- Inspect parameter and plan-cache behavior. If the query is fast with one literal but slow from the application, test generic vs custom plans, bind values, SQL Server parameter sniffing/PSP/OPPO behavior, and Oracle bind-aware cursor behavior.
- Inspect plan history. If performance regressed after deploy, upgrade, stats refresh, index change, or data growth, compare old and new plans through Query Store, AWR, saved JSON/XML plans, SQL Plan Management, or monitoring.
- Choose the response and verify. Re-run the same representative plan and workload query. Confirm the chosen node changed, estimated/actual rows improved, latency and reads improved, and no adjacent query regressed.
How to Read the Plan
| Evidence | What to ask | Typical diagnosis |
|---|---|---|
| Estimated rows vs actual rows | Where does the first large divergence appear? | Statistics, correlation, parameter sensitivity, stale partition stats, missing constraints |
loops |
Is a cheap inner node repeated thousands or millions of times? | Nested loop explosion, N+1-like shape, bad join order |
| Actual time | Is this inclusive parent time or local node work? | Use tree shape and tooling; do not equate top node time with root cause |
| Buffers / reads / I/O | Is the query CPU-bound, cache-bound, or storage-bound? | Missing access path, too much data, cold cache, sequential/bitmap scan cost |
| Temp reads/writes | Did sort, hash, or materialization spill? | Memory grant / work_mem / grouping strategy / ORDER BY response |
| Rows Removed by Filter | Did the engine fetch many rows and discard them late? | Mismatched index, predicate not pushed down, non-sargable filter |
| Heap Fetches | Did an index-only scan still visit the table? | Visibility map/vacuum issue; covering benefit not real |
| Hash batches / memory | Did a hash join or aggregate overflow memory? | Bad estimate, memory setting, pre-aggregation, different join/aggregate strategy |
| Planning vs execution time | Is repeated compilation the bottleneck? | Prepared statements, plan cache, optimized plan forcing, query shape simplification |
| PostgreSQL JIT timing | Does JIT generation/inlining/optimization/emission time dwarf execution time for a short OLTP query? | Disable jit, raise JIT cost thresholds, or leave JIT for analytical queries where execution savings exceed compilation cost |
| Settings and compatibility level | Did a session, compatibility level, or upgrade change planner behavior? | Regression, feature gate, hint/plan guide, database compatibility level |
| Waits and locks | Is the query fast alone but slow under concurrency? | Blocking, lock contention, vacuum/dead tuple pressure, isolation interaction, pool saturation |
The Plan-Node Catalog
| Plan node | What it does | Read as |
|---|---|---|
| Seq Scan / Table Scan | Reads every row in a table or partition | Correct for small tables or low-selectivity predicates; suspicious for large selective predicates |
| Index Scan / Index Seek | Uses index to locate rows, then fetches base rows | Good for selective predicates; check late filters and random I/O |
| Index Only Scan / Covering Seek | Uses index without base-row fetch when visibility/coverage allows | Best read path when heap/base fetches are low; in PostgreSQL check Heap Fetches and visibility map state |
| Bitmap Index Scan + Bitmap Heap Scan | Builds row-location bitmap, then fetches pages | Good for medium selectivity or AND/OR combinations; check lossy rechecks and heap blocks |
| B-tree Skip Scan / Index Skip Scan | Uses a multicolumn index even when a leading column is not constrained, by repeatedly seeking distinct leading values | Useful only for specific low-cardinality-leading-column cases; verify engine/version and EXPLAIN rather than assuming leftmost-prefix folklore |
| Nested Loop | For each outer row, execute inner lookup/scan | Fast for small outer side and indexed inner; explosive for large outer side |
| Hash Join | Build hash table from one side, probe with other | Good for medium/large equi-joins; check hash batches, memory, and spills |
| Merge Join | Merge sorted inputs on join key | Good when inputs are already sorted or sort cost is paid once |
| Semi Join / Anti Join | Tests existence or non-existence | Often produced by EXISTS, IN, NOT EXISTS, NOT IN; NULL semantics matter |
| Hash Right Anti Join / right anti variant | Anti-join with sides swapped by the optimizer | PostgreSQL 16+ can expose right anti variants; read memory/build side and preserved side carefully, not as a separate SQL syntax to force |
| Adaptive Join | SQL Server can defer join choice until runtime for eligible batch-mode plans | Handles cardinality uncertainty, but still needs row estimates and actual execution evidence |
| Sort | Sorts rows | Expensive at scale; check incremental sort, top-N, memory, and temp spills |
| Incremental Sort | Sorts within already-presorted groups | Good when prefix order exists; sensitive to row estimates and input order |
| Hash Aggregate | Groups through hash table | Good for moderate grouping; check memory and spill batches |
| Group Aggregate | Groups sorted input | Good when sorted order already exists or sort is cheaper than hashing |
| Materialize | Stores intermediate rows for reuse | Can be good reuse or accidental work; check size, loops, and spill |
| Memoize | Caches repeated parameterized inner lookups | Good when repeated keys occur; check hit/miss estimates and actuals where available |
| CTE Scan | Scans a common table expression result | In current PostgreSQL, single-use side-effect-free CTEs can be folded; multiple references or MATERIALIZED can fence predicate pushdown |
| Gather / Gather Merge | Combines parallel worker output | Check worker count, skew, leader work, and whether parallelism helped |
| Limit / Top-N | Stops after enough rows | Can choose startup-cost plans; good for true row goals, bad when the row goal is accidental or later expanded |
Root Cause to Response Catalog
| Diagnosis | Right response |
|---|---|
| Query has high aggregate impact | Tune it before a rarer long query; use workload stats, not anecdotes |
| Sequential/table scan on large table and predicate is selective | Handoff to indexing-strategy for an index candidate; verify with EXPLAIN before and after |
| Sequential/table scan and predicate is not selective | Accept the scan, reduce returned rows, precompute, partition, remodel, or cache; an index may not help |
| Existing index ignored | Check selectivity, stale stats, function/cast/collation mismatch, wrong operator, partial predicate implication, generic plan, and version-specific skip-scan behavior |
| Index scan has high rows removed by filter | Index shape does not match the query; consider composite/partial/expression index through indexing-strategy or rewrite predicate |
| Estimate much less than actual | ANALYZE; raise statistics target; add extended statistics / histograms; check correlated predicates and stale partition stats |
| Estimate much greater than actual | Refresh stats; expose constraints/selectivity; check type mismatch; rewrite predicate |
| Correlated predicates misestimated | PostgreSQL extended statistics, SQL Server CE feedback/hints as last resort, MySQL histograms, or schema/model change |
| MySQL histogram needed | Use full syntax such as ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS AUTO UPDATE; AUTO UPDATE is valid in MySQL 9.7 only as part of `UPDATE HISTOGRAM ... [{MANUAL |
| Literal fast but parameterized app query slow | Investigate generic vs custom plan, parameter sniffing, PSP/OPPO, bind peeking, dynamic SQL, OPTION (RECOMPILE), or plan-cache settings |
Optional-parameter predicate such as col = @p OR @p IS NULL is slow |
On SQL Server 2025+ / Azure SQL check OPPO eligibility; otherwise split query shapes or use dynamic SQL so seek and scan cases have different plans |
| Nested Loop with large outer side | Fix upstream estimate, add/adjust inner access path, rewrite to enable hash/merge, or change join order only as measured last resort |
| Hash Join spills or has many batches | Fix estimates, increase appropriate memory grant/work_mem carefully, pre-filter, pre-aggregate, or change join strategy |
| Sort spills to temp | Add order-compatible index through indexing-strategy, reduce rows before sort, use top-N/LIMIT shape, tune memory carefully, or accept if rare |
| PostgreSQL JIT compilation time dominates a short OLTP query | Test SET jit = off for the representative case; if it wins, disable JIT for that workload or raise jit_above_cost / related thresholds, while retaining JIT for analytical queries that benefit |
| Many Sort nodes | Check whether ORDER BY/GROUP BY can use existing order, incremental sort, or a deliberate index; avoid sorting data you later discard |
| CTE or derived table blocks pushdown | For PostgreSQL, test NOT MATERIALIZED for side-effect-free CTEs that should be folded; test MATERIALIZED when reuse prevents repeated expensive work |
| Correlated subquery slow | Rewrite as EXISTS, semi-join, join plus aggregate, or lateral join as appropriate; verify semantic equivalence |
| N+1 application query pattern | Replace with one set-based query, batched lookup, or data-loader pattern; query optimization can diagnose but app code owns the fix |
| Query slow only under concurrency | Check wait events, locks, blocking, isolation, dead tuples/vacuum, and connection pool saturation; hand off correctness to transaction-isolation |
| Plan regression after upgrade, stats refresh, or index change | Compare plan history; use Query Store / SQL Plan Management / saved plans; force known-good plan only while root cause is fixed |
| Hint seems necessary | Treat hints, plan guides, Query Store hints, and plan advice as last-resort evidence-backed controls; revisit after upgrades, data growth, and compatibility changes |
| Query fundamentally scans/joins/aggregates too much | Materialized view, precomputed aggregate, denormalization, partitioning/sharding, data-model change, or application cache |
Query Rewrite Patterns
- Make predicates sargable. Avoid wrapping indexed columns in functions, implicit casts, mismatched collations, leading wildcards, broad OR conditions, or type mismatches when a clearer predicate shape is possible.
- Split incompatible shapes. A single query that must serve "all rows" and "one selective value" may force one bad reusable plan. Split scan and seek cases when the engine cannot do it automatically.
- Use
EXISTSfor existence.EXISTS/ semi-join shapes often express intent better than join-plus-distinct or correlated row-by-row tests. - Push filters earlier when semantics allow. Reduce rows before joins, sorts, and aggregates. Verify that rewrite preserves NULL and duplicate semantics.
- Be precise with CTEs. In current PostgreSQL, side-effect-free single-use CTEs can be folded into the parent query; multiply referenced or
MATERIALIZEDCTEs can block predicate pushdown. UseNOT MATERIALIZEDonly when duplicate work is acceptable. - Avoid accidental row goals.
LIMIT,TOP,EXISTS, and cursor patterns can favor startup-cost plans that are great for a few rows and bad when the row goal is wrong. - Preserve semantic equivalence. Every rewrite must be checked for NULL behavior, duplicate rows, aggregation level, transaction visibility, and ordering assumptions.
Modern Engine Features and Upstream Displacement Check
No current upstream release makes this skill obsolete. The trend is partial automation and richer evidence.
- PostgreSQL 18 (current stable docs in this pass) adds asynchronous I/O that can change sequential/bitmap scan performance,
pg_upgraderetention of optimizer statistics, and B-tree skip scan that lets some multicolumn indexes be used when older versions would not. Do not apply old leftmost-prefix conclusions without checking engine version and EXPLAIN. PostgreSQL JIT is primarily beneficial for long-running CPU-bound analytical queries; if EXPLAIN shows JIT generation/emission time dominating a short query, testjit = offor higher JIT thresholds before changing indexes. - PostgreSQL 19 Beta 1 (released 2026-06-04, not production guidance) previews planner improvements,
pg_plan_advice/pg_stash_advice,EXPLAIN ANALYZE IO, Memoize estimates in EXPLAIN, more optimizer rewrites including anti-join improvements, and JIT off by default. Treat this as test evidence and future-facing source until GA. - SQL Server 2022/2025 and Azure SQL move more tuning into Query Store and Intelligent Query Processing: PSP handles non-uniform parameter values, OPPO handles optional-parameter NULL-vs-not-NULL plan choices, CE/DOP/memory feedback improve repeated queries, Query Store hints shape plans without app-code edits, and automatic plan correction can force a last known good plan. These features address specific classes; they still require workload baselines, correctness checks, and hint debt review.
- MySQL 9.7 current manuals expose EXPLAIN ANALYZE in TREE/JSON form, Performance Schema profiling, slow logs, and histogram statistics.
SHOW PROFILE/SHOW PROFILESare deprecated. Histograms can improve row estimates without write-time index overhead, but they are statistics objects with explicitUPDATE HISTOGRAM/DROP HISTOGRAMmanagement and optionalAUTO UPDATEbehavior. - Oracle distinguishes explained plans from actual cursor plans. Use
DBMS_XPLAN.DISPLAY_CURSOR, SQL Monitor, AWR, and plan-related views for actual execution context; use SQL Plan Management for plan-regression control. Oracle SQL Analysis Report advice in DBMS_XPLAN/SQL Monitor is useful when available, but treat it as diagnostic advice to verify against actual plans, not as an autonomous fix.
Verification
After applying this skill, verify:
- The query was prioritized by aggregate workload impact, not only by the most dramatic single execution.
- The plan came from the representative query shape: same bind values, data distribution, session settings, engine version, compatibility level, and prepared/literal path as the slow case.
-
EXPLAIN ANALYZEor actual-plan capture was used safely; DML was not executed destructively outside rollback/staging controls. - Structured plan output such as JSON/XML/YAML/TREE was used when an agent/script inspected the plan.
- Estimated rows vs actual rows was checked at every node, and the first major divergence was identified.
- The plan tree was read with loops, child-inclusive work, buffers/reads, temp I/O, memory, WAL, rows removed, heap fetches, hash batches, sort spills, parallel workers, and waits.
- Index-only scan claims were checked against heap fetches / visibility-map or engine-equivalent evidence.
- PostgreSQL JIT timing was checked when compilation appears in EXPLAIN output or planning/compilation time dominates a short query.
- Parameter-sensitive behavior was checked when literals and bind-parameter executions differ.
- Temp-file or spill responses accounted for row reduction, estimate fixes, and per-query memory budgeting before raising global memory settings.
- The response matches the diagnosis; "add an index" is only one response.
- Query rewrites were tested for semantic equivalence and plan-shape change.
- Statistics refresh or richer statistics were tried before deeper surgery when estimates were wrong.
- Engine-specific automation and hints were treated as candidate responses, not as proof; any forced plan, hint, plan advice, or SQL Analysis Report recommendation has an owner and review trigger.
- Durable index, schema, migration, isolation, or load-test work was handed to the appropriate adjacent skill.
- The query was re-measured after the change, and adjacent regressions were checked.
Do NOT Use When
| Instead of this skill | Use | Why |
|---|---|---|
| Designing which indexes the database should maintain | indexing-strategy |
This skill diagnoses one query; indexing-strategy owns durable index portfolio tradeoffs |
| Writing the production DDL to add/drop the chosen index | database-migration |
Migration owns locks, online build options, rollback, and deployment safety |
| Designing schema, entity relationships, denormalization, or materialized views | entity-relationship-modeling |
Query optimization may diagnose the need, but entity-relationship-modeling owns the model |
| Reasoning about schema changes over time | schema-evolution |
Schema-evolution owns versioning and lifecycle |
| Choosing isolation level for correctness | transaction-isolation |
Isolation owns concurrency correctness; this skill only flags waits/locks as a performance symptom |
| Horizontal partitioning across nodes | sharding-strategy |
Sharding owns cross-node partitioning; this owns within-query plan diagnosis |
| Designing whole-system performance tests | performance-testing |
Performance-testing owns load shape and system behavior; this diagnoses query plans |
Key Sources
- PostgreSQL Global Development Group. "EXPLAIN" and "Using EXPLAIN": https://www.postgresql.org/docs/current/sql-explain.html and https://www.postgresql.org/docs/current/using-explain.html. Provenance: official PostgreSQL reference for EXPLAIN options, safe ANALYZE use, structured output, plan tree interpretation, buffers, rows removed, and node examples.
- PostgreSQL Global Development Group. "Index-Only Scans and Covering Indexes": https://www.postgresql.org/docs/current/indexes-index-only-scans.html. Provenance: official reference for visibility-map caveats and heap visits during index-only scans.
- PostgreSQL Global Development Group. "Statistics Used by the Planner" and "CREATE STATISTICS": https://www.postgresql.org/docs/current/planner-stats.html and https://www.postgresql.org/docs/current/sql-createstatistics.html. Provenance: official reference for statistics, extended statistics, functional dependencies, n-distinct, and MCV lists.
- PostgreSQL Global Development Group. "PREPARE" and "Query Planning": https://www.postgresql.org/docs/current/sql-prepare.html and https://www.postgresql.org/docs/current/runtime-config-query.html. Provenance: official reference for generic vs custom plans,
plan_cache_mode, Memoize, and Incremental Sort planner controls. - PostgreSQL Global Development Group. "Resource Consumption": https://www.postgresql.org/docs/current/runtime-config-resource.html. Provenance: official reference for
work_mem, sort/hash memory, hash memory multiplier, and concurrency multiplication. - PostgreSQL Global Development Group. "When to JIT?": https://www.postgresql.org/docs/current/jit-decision.html. Provenance: official reference for when JIT helps or hurts, EXPLAIN JIT timing,
jit, and JIT cost thresholds. - PostgreSQL Global Development Group. "WITH Queries": https://www.postgresql.org/docs/current/queries-with.html. Provenance: official current CTE folding/materialization rules.
- PostgreSQL Global Development Group. "pg_stat_statements" and "auto_explain": https://www.postgresql.org/docs/current/pgstatstatements.html and https://www.postgresql.org/docs/current/auto-explain.html. Provenance: official reference for workload ranking and automatic slow-plan capture.
- PostgreSQL Global Development Group. "PostgreSQL 16 Release Notes": https://www.postgresql.org/docs/release/16.0/. Provenance: official release-note evidence for anti-join and parallel hash-join planner changes.
- PostgreSQL Global Development Group. "PostgreSQL 18 Release Notes": https://www.postgresql.org/docs/release/18.0/. Provenance: official current-stable release notes for AIO, optimizer-stat retention, and B-tree skip scan.
- PostgreSQL Global Development Group. "PostgreSQL 19 Beta 1 Released" and "PostgreSQL 19 Release Notes": https://www.postgresql.org/about/news/postgresql-19-beta-1-released-3313/ and https://www.postgresql.org/docs/19/release-19.html. Provenance: official beta source for future-facing upstream-displacement check; beta, not production guidance.
- MySQL Reference Manual. "EXPLAIN Statement," "Optimizer Statistics," "ANALYZE TABLE Statement," and "SHOW PROFILES Statement": https://dev.mysql.com/doc/refman/9.7/en/explain.html, https://dev.mysql.com/doc/refman/9.7/en/optimizer-statistics.html, https://dev.mysql.com/doc/refman/9.7/en/analyze-table.html, and https://dev.mysql.com/doc/refman/9.7/en/show-profiles.html. Provenance: official MySQL current manual for EXPLAIN ANALYZE TREE/JSON, histograms including
AUTO UPDATE, and SHOW PROFILE deprecation. - Microsoft Learn. "Monitor performance by using the Query Store": https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store. Provenance: official SQL Server reference for workload history, plan regressions, and plan forcing.
- Microsoft Learn. "Parameter Sensitive Plan Optimization," "Optional Parameter Plan Optimization," "Query Store hints," and "Automatic tuning": https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization, https://learn.microsoft.com/en-us/sql/relational-databases/performance/optional-parameter-optimization, https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints, and https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning. Provenance: official SQL Server sources for PSP, OPPO, hints, and automatic plan correction.
- Oracle Help Center. "Explaining and Displaying Execution Plans" and "Overview of SQL Plan Management": https://docs.oracle.com/en/database/oracle/oracle-database/26/tgsql/generating-and-displaying-execution-plans.html and https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/overview-of-sql-plan-management.html. Provenance: official Oracle sources for explained-vs-actual plan distinction, DBMS_XPLAN display functions, cursor/AWR views, and SQL Plan Management.
- Oracle Optimizer Blog. "New SQL Analysis Report in Oracle Database 23c Free!": https://blogs.oracle.com/optimizer/sql-analysis-report-in-23c-free. Provenance: Oracle-authored source for SQL Analysis Report in DBMS_XPLAN and SQL Monitor; used to qualify, not overstate, advisory Oracle SQL Analysis Report claims.
- Selinger, P. G., Astrahan, M. M., Chamberlin, D. D., Lorie, R. A., and Price, T. G. (1979). "Access Path Selection in a Relational Database Management System": https://dl.acm.org/doi/10.1145/582095.582099. Provenance: foundational System R cost-based optimization paper.
- Tow, D. (2003). SQL Tuning. O'Reilly. Provenance: classic database-agnostic practitioner methodology for SQL tuning.
- Petrov, A. (2019). Database Internals. O'Reilly. Provenance: deeper storage and execution-model background.
- Kleppmann, M. (2017). Designing Data-Intensive Applications. O'Reilly. Provenance: storage/retrieval and data-system framing behind plan shapes.
- Winand, M. Use The Index, Luke!: https://use-the-index-luke.com/. Provenance: practitioner guide for how index shape interacts with query plans; use as secondary explanation beside vendor docs.
Skill Graph context
Classification
- Subject:
data-engineering - Public:
true - Domain:
engineering/data - Scope: Diagnosing and tuning a specific slow query in a relational database by reading optimizer evidence: workload ranking, exact SQL and parameter context, safe plan capture, estimated-vs-actual rows, access paths, join methods, sort/hash/temporary I/O, memory, buffers, parallelism, JIT/planning overhead, parameter-sensitive plan behavior, plan regressions, statistics quality, and response selection. Portable across PostgreSQL, MySQL, SQL Server, Oracle, and relational engines with a cost-based optimizer. Excludes durable index-portfolio design (indexing-strategy), schema/entity modeling (entity-relationship-modeling), distributed partitioning (sharding-strategy), transaction-correctness isolation choices (transaction-isolation), and whole-system load/performance testing (performance-testing).
When to use
- diagnose this EXPLAIN ANALYZE BUFFERS output and identify the real bottleneck
- this query is fast with a literal but slow from the app with bind parameters
- the plan changed after an upgrade and Query Store shows a regression
- rewrite this correlated subquery or optional-parameter predicate so the optimizer can use a better plan
- decide whether the fix is ANALYZE, extended statistics, a histogram, an index, a rewrite, a materialized view, or accepting the scan
- Triggers:
this query is slow,EXPLAIN ANALYZE output,why isn't the planner using the index,bad row estimate,parameter sniffing,generic vs custom plan,query plan changed,plan regression,sort spilled,slow only in production
Not for
- design the index set for a new workload (use indexing-strategy)
- choose the database schema and keys (use entity-relationship-modeling)
- write the production migration for a new index (use database-migration)
- choose isolation levels for correctness (use transaction-isolation)
- design a load test for the whole service (use performance-testing)
Related skills
- Verify with:
entity-relationship-modeling,indexing-strategy,transaction-isolation,performance-testing - Related:
schema-evolution,indexing-strategy,entity-relationship-modeling,transaction-isolation,replication-patterns,performance-testing,database-migration
Concept
- Mental model: |
- Purpose: |
- Boundary: |
- Analogy: Query optimization is chart reading for a slow SQL statement: the plan is the chart, estimates are the diagnosis the optimizer made before treatment, actual rows and I/O are the lab results, and the intervention should follow the mismatch rather than the most familiar prescription.
- Common misconception: |
Grounding
- Mode:
universal - Truth sources:
https://www.postgresql.org/docs/current/sql-explain.html,https://www.postgresql.org/docs/current/using-explain.html,https://www.postgresql.org/docs/current/indexes-index-only-scans.html,https://www.postgresql.org/docs/current/planner-stats.html,https://www.postgresql.org/docs/current/sql-createstatistics.html,https://www.postgresql.org/docs/current/sql-prepare.html,https://www.postgresql.org/docs/current/runtime-config-query.html,https://www.postgresql.org/docs/current/runtime-config-resource.html,https://www.postgresql.org/docs/current/jit-decision.html,https://www.postgresql.org/docs/current/queries-with.html,https://www.postgresql.org/docs/current/pgstatstatements.html,https://www.postgresql.org/docs/current/auto-explain.html,https://www.postgresql.org/docs/release/16.0/,https://www.postgresql.org/docs/release/18.0/,https://www.postgresql.org/about/news/postgresql-19-beta-1-released-3313/,https://www.postgresql.org/docs/19/release-19.html,https://dev.mysql.com/doc/refman/9.7/en/explain.html,https://dev.mysql.com/doc/refman/9.7/en/optimizer-statistics.html,https://dev.mysql.com/doc/refman/9.7/en/analyze-table.html,https://dev.mysql.com/doc/refman/9.7/en/show-profiles.html,https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store,https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization,https://learn.microsoft.com/en-us/sql/relational-databases/performance/optional-parameter-optimization,https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints,https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning,https://docs.oracle.com/en/database/oracle/oracle-database/26/tgsql/generating-and-displaying-execution-plans.html,https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/overview-of-sql-plan-management.html,https://blogs.oracle.com/optimizer/sql-analysis-report-in-23c-free
Keywords
query optimization,EXPLAIN ANALYZE,execution plan,query planner,cardinality estimate,parameter sniffing,generic plan,Query Store,pg_stat_statements,slow query