query-optimization — independently scanned and version-tracked by SaferSkills.
SaferSkills independently audited query-optimization (Agent Skill) and scored it 100/100 (green). The audit ran 55 deterministic rules across Security, Supply Chain, Maintenance, Transparency, and Community; it found 0 high-severity and 0 lower-severity findings. The full rule-by-rule trace and per-finding evidence are below. Free, methodology-open.
Findings & checks · 0 flagged
Every scanned point with the score it earned and what moved between them.
First recorded scan — no prior version to compare against.
The primary manifest — the file an agent reads to learn what this artifact does.
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.
The discipline of diagnosing and tuning specific slow relational queries by reading actual plan evidence and matching the response to the root cause. Covers:
pg_stat_statements, auto_explain, DBMS_XPLAN, SQL Monitor, MySQL EXPLAIN ANALYZE, Performance Schema, or slow logs; how to avoid side effects for DML.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.
Use this ladder before proposing a fix:
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.SELECT, prefer actual runtime plans. In PostgreSQL, use EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, MEMORY, FORMAT JSON) when available; add PostgreSQL 19 beta IO only when testing AIO behavior on a beta system. For DML, remember that EXPLAIN ANALYZE executes the statement; use a transaction plus rollback, a safe replica, staging data, or plain EXPLAIN when side effects cannot be allowed.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.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| 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 |
| 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 |
| 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 | AUTO} UPDATE]` |
| 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 |
EXISTS / semi-join shapes often express intent better than join-plus-distinct or correlated row-by-row tests.MATERIALIZED CTEs can block predicate pushdown. Use NOT MATERIALIZED only when duplicate work is acceptable.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.No current upstream release makes this skill obsolete. The trend is partial automation and richer evidence.
pg_upgrade retention 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, test jit = off or higher JIT thresholds before changing indexes.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.SHOW PROFILE / SHOW PROFILES are deprecated. Histograms can improve row estimates without write-time index overhead, but they are statistics objects with explicit UPDATE HISTOGRAM / DROP HISTOGRAM management and optional AUTO UPDATE behavior.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.After applying this skill, verify:
EXPLAIN ANALYZE or actual-plan capture was used safely; DML was not executed destructively outside rollback/staging controls.| 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 |
plan_cache_mode, Memoize, and Incremental Sort planner controls.work_mem, sort/hash memory, hash memory multiplier, and concurrency multiplication.jit, and JIT cost thresholds.AUTO UPDATE, and SHOW PROFILE deprecation.~30 seconds. Free. No account. Every finding cites a rule and a line of evidence.