AI Slow Query Optimizer

Database query optimization is the kind of skill that takes years to develop because it requires fluency in three things at once: the query language, the engine's execution model, and the specific schema. The same SQL produces different plans on different schemas, the same plan produces different costs on different data sizes, and the same data produces different bottlenecks under different concurrency. Mastering this is what makes a senior data engineer expensive. The result is that most teams have one or two people who can read an execution plan and understand it, and most slow queries wait their turn in those people's queues.

Query optimization is also surprisingly compressible. The patterns that make queries slow are a finite list: missing indexes, bad join order, sequential scans on large tables, subqueries that should be joins, joins that should be subqueries, ORM-generated IN (...) lists with thousands of values. The fix for each is well-understood. The diagnosis step ("which of these is happening here") is what takes the experienced eye, and that is exactly what /flux-query is built to encode. Hand it the slow query and the schema, and it reads the plan, identifies the pattern, suggests the rewrite, and explains why the rewrite helps. The team learns the reasoning while the immediate query gets faster.

Why generalist AI cannot tune queries

Ask Cursor or ChatGPT to optimize a query and you get suggestions that look reasonable: "add an index, rewrite the subquery as a join, consider denormalizing." The suggestions are based on the SQL alone, not on the actual execution plan. Without the plan, the model is guessing at what the engine is doing. The guess is sometimes right and often wrong, because the engine's choices depend on table statistics that the model cannot see (cardinality estimates, index correlation, table sizes). The advice that comes out of a generalist tool optimizes for the SQL it was shown rather than the workload the SQL runs against.

The other failure mode is the index suggestion bias. A generalist tool will suggest an index for almost any slow query because indexes feel like the safe answer. Some indexes help, some are redundant with existing indexes, some are net-negative because the write cost outweighs the read benefit. The right answer depends on the query patterns the table actually serves, which the model cannot see without reading the schema and the workload together. /flux-query is built to read both.

What query optimization actually requires

A useful query optimization pass has three steps. First, get the execution plan with actual run statistics (EXPLAIN ANALYZE in Postgres, EXPLAIN ANALYZE FORMAT=JSON in MySQL). The plan shows what the engine actually did, not what it might do. Second, identify the dominant cost in the plan: a sequential scan on a large table, a nested loop with the wrong inner side, a hash join that spilled to disk, a sort that did not have enough work_mem. Third, propose the change that addresses the dominant cost: an index, a query rewrite, a parameter tweak, occasionally a schema change. Each change is verified against the plan to confirm the cost moved.

The discipline is the same as backend performance: localize the cost first, then propose the fix that addresses it. Skipping the localization step (jumping to "add an index") wastes effort on indexes that do not help. The hard part is reading the plan, which is what /flux-query does on the team's behalf, and explaining why the proposed change addresses the dominant cost, which is what makes the optimization defensible in review.

How /flux-query works

Step one: pull the plan with run statistics

When invoked with a slow query, /flux-query runs EXPLAIN ANALYZE (or the engine equivalent) against a representative dataset and parses the resulting plan. The plan tells the skill what the engine actually did: which tables were scanned, which indexes were used, which joins ran in which order, where time was spent. The plan is the input to the diagnosis.

Step two: identify the dominant cost

The skill identifies the highest-cost node in the plan and characterizes it: "sequential scan on events (12M rows, 8.3s)" or "nested loop with 1.2M iterations of the inner side." The dominant cost determines the fix candidate set. A sequential scan suggests an index; a nested loop with too many iterations suggests a join order change or a hash join hint; a sort that spilled suggests a work_mem adjustment or an index that provides pre-sorted output.

Step three: propose the change with reasoning

The skill proposes the change and explains why it addresses the dominant cost. "Add events(account_id, created_at DESC) index. The query filters by account and orders by recency; the new index serves both. Estimated effect: replace the sequential scan with an index scan, dropping cost from 12M-row scan to ~50 row index seek + read. Side effect: writes to events get one more index to maintain; the table is read-heavy so this is acceptable." The reasoning is the part that lets the team apply the same logic to future queries; the index is the side effect.

Step four: verify against the plan

After the change is applied, /flux-query reruns EXPLAIN ANALYZE to confirm the dominant cost dropped. The verification is the proof. If the new plan still shows the original bottleneck, the diagnosis was wrong and the skill loops back to step two. If the cost moved as expected, the optimization is done.

Tonone's /flux-query skill optimizes slow database queries by reading the execution plan, identifying the dominant cost, and proposing rewrites with reasoning so the team can apply the same logic to future queries.

When to use /flux-query, and when not to

/flux-query is the right call when specific queries are slow in production or staging and the cause is not clear, when pg_stat_statements or slow query logs show high-cost queries, or when preparing for a traffic spike and the team wants to identify database bottlenecks before load arrives.

Skip the skill for endpoint-level performance issues that may not be at the query layer (use /spine-perf to localize first). For schema design from scratch, /flux-schema is the right call. For zero-downtime evolution of an existing schema, /flux-migrate produces the multi-step plan.

A worked example: optimizing a slow report query

Suppose the brief is: this finance report query takes 14 seconds. Run /flux-query against it.

-- Original query
SELECT customer_id, SUM(amount_cents) AS total
FROM invoices
WHERE status = 'paid'
  AND paid_at >= NOW() - INTERVAL '30 days'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

-- Plan summary (EXPLAIN ANALYZE)
-- Seq Scan on invoices (cost=0..298410, rows=8.2M, actual=12.8s)
--   Filter: (status = 'paid' AND paid_at >= NOW() - 30 days)
--   Rows Removed: 7.1M
-- Sort + GroupAggregate downstream (~0.9s)
-- Total: 14.1s

-- Diagnosis: sequential scan dominates (91% of time).
-- Filter eliminates 7.1M of 8.2M rows; only 1.1M relevant.
-- An index on (status, paid_at) would let the engine read
-- only the relevant rows.

-- Recommended change
CREATE INDEX CONCURRENTLY invoices_paid_recent_idx
  ON invoices (paid_at DESC)
  WHERE status = 'paid';

-- Reasoning: partial index keyed on paid_at descending,
-- only including paid rows. Query becomes:
-- - index scan finds the last 30 days of paid invoices
-- - hash aggregate by customer_id
-- - sort by total + limit 100
-- Expected total: ~120ms (100x improvement).

-- Verification (after applying):
-- Index Scan using invoices_paid_recent_idx (cost=0..14200, actual=87ms)
-- HashAggregate (~28ms)
-- Sort + Limit (~5ms)
-- Total: 120ms. Matches estimate; optimization complete.

The optimization is defensible because the reasoning is on the page. The next engineer who has a similar query (filter on a status, recency-ordered) can follow the same logic without rediscovering it. That is what query tuning looks like when the discipline is encoded; without it, the team is reinventing the same partial-index pattern every quarter.

Related skills

/flux-query optimizes individual queries. For broader endpoint performance diagnosis, /spine-perf localizes the issue first. For schema design including the right indexes from the start, /flux-schema is the right call. For evolving an existing schema under traffic (including index additions), /flux-migrate writes the migration.

Install

/flux-query ships with the Flux agent in the Tonone for Claude Code package. Install Tonone, configure database access, and the skill optimizes queries against actual execution plans.

Slow queries are usually one rewrite or one index away from fast. The skill is built so the rewrite is grounded in the plan, not in intuition.