This is a submission for the Gemma 4 Challenge: Build with Gemma 4

What I Built

I am a Staff DBA at a top public healthcare company. I look after MS SQL Server and PostgreSQL fleets, and every one of our database servers is on-prem and air-gapped. We do not have the option to use a frontier LLM for production work, because of HIPAA. Query plans, table names, filter literals, none of it is allowed to leave our network.

That means when one of our Postgres instances starts misbehaving at 3 AM, I cannot drop an EXPLAIN plan into frontier models like ChatGPT or Claude and ask what changed. I read the JSON by hand, the way I did in 2015. Most of my colleagues do the same.

I built Plansmith for myself. You feed it two EXPLAIN (ANALYZE, FORMAT JSON) outputs, a baseline from when the query was healthy and the incident plan from now, and it gives you a triage runbook in plain English: what changed in the plan, ranked root cause hypotheses, things you can try in the next five minutes, and the durable fix. The whole pipeline runs locally, including the model. The only outbound network call Plansmith makes is to 127.0.0.1:11434, which is Ollama on the same laptop.

The piece that makes a small local model work for this is that Gemma 4 is never asked to read the raw plan JSON. A deterministic Python pass walks both plan trees first and pulls out a structured diff. Scan method flipped from Index Scan to Seq Scan. Planner row estimate off by 124,000x. Sort spilled to disk, hash join needed 8 batches, lost the parallel workers it had before. Only that compact set of named findings goes to the model, with the numbers already measured. Gemma’s job is the part it is actually good at, which is producing the human explanation and the ranked runbook on top of evidence I have already extracted.

Demo

The CLI run on a real sample that ships with the repo, a parameter-sniffing regression on a customer-history query:

$ plansmith analyze \
    --baseline samples/baseline_q1.json \
    --incident samples/incident_q1.json \
    --query    samples/q1.sql

╭─────────────────── Plansmith ───────────────────╮
│ baseline 15ms · incident 7421ms · 497.7× slower │
╰─────────────────────────────────────────────────╯
                    Structured diff findings
┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Severity ┃ Kind               ┃ Summary                           ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ CRITICAL │ runtime_regression │ Execution time grew 497.7×        │
│ CRITICAL │ scan_method_flip   │ orders: Index Scan → Seq Scan     │
│ CRITICAL │ row_estimate_miss  │ Seq Scan on orders off by 124158× │
│ CRITICAL │ nested_loop_blowup │ Nested Loop with 248,317 outer    │
└──────────┴────────────────────┴───────────────────────────────────┘

─────────── Triage report from gemma4:e4b ───────────

And then Gemma 4 streams the runbook in. This is verbatim from a local run on my laptop, no editing:

TL;DR

The planner incorrectly switched to a Sequential Scan on orders because statistics are stale, leading to massive row overestimation and a subsequent join blowup.

Likely root causes (ranked)

1. Stale statistics on the orders table, causing the planner to drastically underestimate the cardinality of the filtered result set. (supported by finding row_estimate_miss)
2. The lack of a suitable composite index covering the WHERE clause columns (customer_id, placed_at) or the join key. (supported by finding scan_method_flip)
3. The planner incorrectly choosing a Nested Loop join due to the highly inflated row estimates. (supported by finding nested_loop_blowup)

Immediate mitigations (minutes)

• Run VACUUM ANALYZE orders; to force an update of statistics on the affected table.
• If the index is known to be correct, temporarily force the planner: SET LOCAL enable_seqscan = off;
• Run EXPLAIN (ANALYZE, BUFFERS) SELECT … to confirm the plan change before deploying any fix.

Permanent fixes (days)

• Create a composite index on orders covering the filter columns: CREATE INDEX idx_orders_customer_time ON orders (customer_id, placed_at);
• Increase the default_statistics_target for orders.customer_id and orders.placed_at to prevent recurrence.
• If the customer base is highly skewed, consider partitioning the orders table by customer_id.

That is roughly what I would have written myself, in maybe two minutes instead of twenty. The second sample in the repo is a different shape of bug, statistics drift on an analytics rollup that ends up 92x slower with a sort spill and a hash spill, and the runbook for that one is just as good.

There is also a small Flask web UI, plansmith serve, that gives you two textareas, a button, and a streamed Markdown panel. It is the same code path, just over Server-Sent Events so the report appears as Gemma generates it. Useful when you are sitting next to a colleague and want to talk through what the model is suggesting.

Code

GitHub: github.com/sireesha-chavvakula/plansmith, Apache-2.0.

plansmith/
├── plansmith/
│   ├── plan_diff.py     # the deterministic EXPLAIN JSON diff
│   ├── triage.py        # Ollama / Gemma 4 prompt and streaming
│   ├── cli.py
│   ├── web.py
│   └── templates/index.html
├── samples/
│   ├── q1.sql, baseline_q1.json, incident_q1.json   # parameter sniffing
│   └── q2.sql, baseline_q2.json, incident_q2.json   # stats drift
├── pyproject.toml
├── LICENSE
└── README.md

About 640 lines of Python and 240 lines of HTML and CSS. Three dependencies, flask, requests, rich. No external services.

The categories of plan regression that plan_diff.py currently detects:

These are the patterns that account for the large majority of plan-shape regressions I have actually triaged in production. The list is short on purpose; I would rather catch the common bugs cleanly than have a long catalog of rules that fire on noise.

How I Used Gemma 4

Why E4B

There are three Gemma 4 variants and the judges want to see why I picked the one I did. For Plansmith the answer is short.

The 31B dense model would write slightly more polished prose. It will not run on the laptop that is sitting next to me when I am on call, and it definitely will not run on the bastion host I have to use to even reach the air-gapped DB network. The 26B MoE is overkill for what is essentially “fill in a six-section template using these eight findings as evidence.” E4B at Q4_K_M is about 8 GB on disk, fits in laptop RAM, streams the runbook fast enough to feel responsive, and the 128K context window means I never have to truncate a plan, even a wide partition-heavy one. It is the smallest Gemma 4 variant that still produces output I would be willing to sign off on, and for an air-gapped DBA tool that is exactly the right tradeoff.

How the prompt is shaped

Gemma never sees raw EXPLAIN JSON. The structured diff produced by plan_diff.py is the entire user message:

{
  "baseline_runtime_ms": 14.91,
  "incident_runtime_ms": 7421.41,
  "slowdown_factor": 497.75,
  "findings": [
    { "kind": "scan_method_flip", "severity": "critical",
      "summary": "Table 'orders' flipped from Index Scan to Seq Scan.",
      "detail": { "relation": "orders", "before": "Index Scan", "after": "Seq Scan" }},
    { "kind": "row_estimate_miss", "severity": "critical",
      "summary": "Seq Scan on orders misestimated rows by 124158×",
      "detail": { "plan_rows": 2, "actual_rows": 248317, "ratio": 124158.5 }}
  ]
}

The system prompt does two things. It tells the model to trust the numbers it was given and not invent new ones, and it locks the output into an exact six-section Markdown skeleton (TL;DR, What the plans say, Likely root causes ranked, Immediate mitigations, Permanent fixes, Verification).

That separation matters more than the prompt wording itself. The deterministic pass does the arithmetic and the pattern recognition. Gemma does the judgment, the ranking, and the prose. A 4B model is plenty for that second job, and it cannot hallucinate row counts because it was never asked to compute any.

Where Plansmith goes next

The two things on my own roadmap:

1. SQL Server support. Half my day is in MSSQL. The XML showplan format is more verbose than Postgres JSON but the structural diff is the same idea, just a different parser. The Gemma prompt does not need to change.
2. Multi-query mode. Feed it a directory of pg_stat_statements plan captures, rank queries by regression severity, produce one runbook per query. This is the actual on-call workflow.

If anyone reading this works in a regulated environment and wants to compare notes on local-only DBA tooling, I would love to hear from you. The “we cannot use the cloud LLMs” group of practitioners is larger than it looks and we are all building the same kinds of tools in parallel.