Performance Investigation: Turn Profiler Output into Ranked Hypotheses

Tier: Core · Workflow area: Debugging & Performance

An AI assistant is excellent at the translation step — converting raw profiler/planner output into ranked bottleneck hypotheses — as long as you keep the confirmation step deterministic: every hypothesis earns a benchmark before it earns a fix.

When to use

• •
  A p95 latency regression, a slow endpoint, or a query that degraded after data growth.
• •
  You have (or can capture) real evidence: a profile, a query plan, GC logs, or APM traces.
• •
  You suspect "it's the database" or "it's serialization" but have not measured.

Do not use AI to guess at bottlenecks without evidence — models pattern-match to popular causes (N+1, missing index) and will confidently confirm your bias.

Prerequisites

• •
  A profiler for your stack: py-spy/cProfile (Python), pprof (Go), async-profiler/JFR (JVM), clinic/0x (Node).
• •
  Database plan access: EXPLAIN (ANALYZE, BUFFERS) in Postgres or EXPLAIN ANALYZE in MySQL 8+.
• •
  A load or benchmark harness you can run twice (before/after): hey, wrk, pytest-benchmark, JMH, or a k6 script.
• •
  Any assistant with a large context window for pasting plans and profile summaries.

The workflow

1. 1.

   Capture evidence under realistic load. Examples:

   Copy

   # Python: 60s sampling profile of the running service, flame graph + top functions
   py-spy record -d 60 -p <PID> -o profile.svg
   py-spy dump --pid <PID>
   
   # Postgres: actual (not estimated) plan with buffer stats
   EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ... ;
   

   Capture at production-like data volume — a plan over 1,000 rows is a different plan than over 50 million.

2. 2.

   Ask for ranked hypotheses, not fixes. Paste the artifact with:

   Copy

   Below is [a py-spy top output / an EXPLAIN ANALYZE plan] from a service whose
   p95 went from 180ms to 900ms after [context: data growth, deploy, traffic].
   
   1. Interpret the artifact: where does actual time go? Quote the specific
      lines/nodes (function %, node actual time, rows expected vs actual, buffer
      reads) that support each claim.
   2. Rank the top 3 bottleneck hypotheses by expected impact.
   3. For each: what single cheap measurement would confirm or kill it?
   Do NOT propose code or index changes yet.
   

   Requiring quoted evidence per claim is what separates interpretation from confabulation — a hypothesis that cannot cite a plan node is a guess.

3. 3.

   Check the classic misread sites yourself. In query plans: rows estimated vs actual mismatches (stale statistics), Rows Removed by Filter (index not selective), buffer reads vs hits (cold cache vs genuine I/O), and whether a nested-loop node's actual time multiplies by loop count. Ask the model to walk each of these explicitly if its summary skipped them.

4. 4.

   Confirm the top hypothesis with a micro-benchmark before fixing. Have the assistant generate the harness:

   Copy

   Hypothesis 1 is the ORDER BY ... LIMIT sort spilling to disk (Sort Method:
   external merge, Disk: 210MB). Generate a benchmark script that runs this
   query 20 times with warm cache against $DATABASE_URL, records p50/p95, and
   prints the sort method used. I will run it before and after any change.
   

   Run it. If the confirming measurement does not move, kill the hypothesis and promote the next one — do not fix anyway.

5. 5.

   Apply the fix, re-run the same harness, keep the receipts. Paste before/after numbers back into the thread and ask for a plain-language summary for the PR description. A perf PR without before/after numbers from an identical harness is an opinion.

Tool variants

• Claude / large-context chat: best for full EXPLAIN (ANALYZE, BUFFERS) plans and multi-hundred-line pprof text dumps pasted whole.
• Copilot Chat in-IDE: convenient for iterating on the benchmark harness next to the code; paste the plan into the chat pane as context.
• Claude Code / agent mode: can run the profiler and benchmark loop itself (profile the test suite, then rerun after the change) — keep the before/after comparison in your hands.

Verification

• •
  Every accepted hypothesis cites specific artifact lines (plan node times, function percentages) that you checked exist and say what is claimed.
• •
  The confirming measurement moved when you fixed the cause (e.g., sort method changed from external merge to quicksort, p95 dropped accordingly).
• •
  The fix was validated by the same harness and dataset as the baseline, warm-cache to warm-cache.
• •
  Estimated-vs-actual row mismatches were either absent or explained (and ANALYZE was run if statistics were stale).

Pitfalls

• Fixing the model's favorite bottleneck. LLMs over-suggest indexes and N+1 fixes because those dominate training data. An index on a column with Rows Removed by Filter: 12 fixes nothing.
• Benchmarking cold vs warm. First run pays the cache penalty; compare steady-state to steady-state or you will "confirm" phantom wins.
• Optimizing a non-representative dataset. Plans flip (seq scan vs index scan) with table size; measure at production scale or a scaled sample with matching statistics.
• Accepting relative claims. "This should be much faster" is not a result. Numbers from your harness or it did not happen.

Restricted-environment variant

• •
  Query plans and profiles often leak schema, table names, and volumetrics. If policy restricts sharing, sanitize first: rename tables/columns to t1/c1 consistently (keep the mapping local) — plan structure is what the model needs, not names.
• •
  Strip literal values from queries (WHERE email = $1, never the actual email) — DPDP-relevant personal data hides in query literals and log lines.
• •
  Local route: an Ollama-hosted coder model handles plan interpretation acceptably; keep hypothesis ranking and confirm with the same deterministic benchmarks — the harness discipline is identical regardless of model quality.

Success criteria

• •
  From artifact to confirmed root cause in under an hour for a typical single-service regression.
• •
  Zero fixes shipped on unconfirmed hypotheses; every perf PR carries identical-harness before/after numbers.
• •
  You can independently explain each plan node or profile line the model cited — the AI accelerated your reading, not replaced it.