ClawHub

Sql Optimization

Workflows

Deep SQL performance workflow—symptom framing, execution plans, indexing strategy, query rewrite, locking/transaction behavior, statistics, partitioning, and verification. Use when queries time out, DB CPU spikes, or migrations change access patterns.

Install

openclaw skills install sql-optimization

SQL Optimization (Deep Workflow)

Optimization without measurement is guesswork. Structure the work as observe → explain (plan) → change → verify, with explicit attention to correctness, locks, and write amplification from indexes.

When to Offer This Workflow

Trigger conditions:

  • Slow queries, growing P95/P99, replication lag, lock waits
  • ORM-generated SQL surprises; N+1 at DB layer
  • Index explosion, bloat, or “we added indexes everywhere”

Initial offer:

Use six stages: (1) frame the problem, (2) reproduce & measure, (3) read execution plans, (4) schema & indexes, (5) query & transaction tuning, (6) verify & guardrail. Confirm engine (PostgreSQL, MySQL, SQL Server, etc.) and environment (prod-like data volume).

Stage 1: Frame the Problem

Goal: Define SLO, scope, and non-goals.

Questions

  1. Which queries or endpoints are slow? User-facing vs batch?
  2. Regression—did deploy, data volume, or stats change?
  3. Isolation level and consistency requirements—can we read replicas?
  4. Write risk: is this table write-heavy? Index cost?

Exit condition: One-line problem statement with metric (e.g., “p95 2.4s on /reports at 10k RPS”).

Stage 2: Reproduce & Measure

Goal: Stable repro with representative cardinality and parameters.

Actions

  • Capture exact SQL, parameters, and frequency
  • Use EXPLAIN (ANALYZE, BUFFERS) or equivalent—engine-specific
  • Check buffer cache effects: cold vs warm cache; run twice when needed
  • Compare prod stats vs staging—row counts, histograms

Pitfalls

  • Optimizing on empty dev DB
  • Different parameter sniffing values changing plan choice

Exit condition: Baseline numbers + plan hash or saved plan for A/B.

Stage 3: Read Execution Plans

Goal: Name the dominant cost: seq scan, bad join order, sort, hash spill, nested loop explosion.

Interpret (adapt to engine)

  • Seq scan on large tables—filter selectivity? missing index? stats?
  • Index scan vs bitmap vs index only—covering indexes trade-offs
  • Joins: wrong order, missing stats, outdated NDV
  • Sort/hash spills to disk—work_mem / memory grants
  • Locks: FOR UPDATE, long transactions, hot row updates

Exit condition: Hypothesis tied to plan node(s), not generic “add index.”

Stage 4: Schema & Indexes

Goal: Right indexes for read paths without destroying writes.

Strategy

  • Composite index column order: equality → range; avoid redundant indexes
  • Partial indexes for hot subsets
  • Covering indexes vs table bloat—measure write cost
  • Foreign keys and constraints affecting plans
  • Statistics: ANALYZE, extended stats, histograms—when stale stats lie

Advanced (when relevant)

  • Partitioning for prune + maintenance
  • Materialized views / pre-aggregation for heavy reports

Exit condition: DDL proposal with rationale and rollback (drop index concurrently if supported).

Stage 5: Query & Transaction Tuning

Goal: Sometimes the fix is SQL rewrite, not hardware.

Techniques

  • Reduce rows touched early (CTEs vs inline—engine-dependent)
  • Pagination without OFFSET on huge pages (keyset)
  • Batch vs row-by-row; UNION ALL vs OR
  • N+1: batch queries, joins, data loader patterns
  • Transactions: shorten locks; avoid unnecessary SELECT FOR UPDATE
  • ORM: eager vs lazy loading discipline

Exit condition: New plan shows lower cost / measured latency; lock time acceptable.

Stage 6: Verify & Guardrail

Goal: Improvement holds under load and doesn’t regress neighbors.

Verify

  • Re-run EXPLAIN ANALYZE with production-like parameters
  • Load test or shadow traffic if available
  • Monitor: buffer hit ratio, index bloat, replication lag

Guardrails

  • Query timeouts and statement_timeout where safe
  • Alerts on sequential scans on large tables if observability supports

Final Review Checklist

  • Baseline and target metrics documented
  • Plan-based root cause, not guesswork
  • Index/DDL changes justified vs write load
  • Transaction/lock behavior considered
  • Verification on realistic data and load

Tips for Effective Guidance

  • Always mention parameter sniffing and stale statistics as frequent culprits.
  • Warn when adding indexes on very write-heavy tables without measuring bloat.
  • Prefer keyset pagination education for large lists.

Handling Deviations

  • No EXPLAIN access: infer from symptoms + ORM logs + index list; recommend safe staging repro.
  • Vendor DB: name that hints and features differ—avoid PostgreSQL-only advice on SQL Server without caveat.
More in Workflows