Performance Tuning with AI
AI is effective at two performance tasks: interpreting profiler output to identify bottlenecks you might overlook, and generating optimisations for common patterns like slow SQL queries or missing indexes. The fundamental rule of performance work does not change — measure first, optimise second. AI cannot measure for you, but it can help you understand what you measured and what to try next.
Profiling Output Analysis
Paste profiler output directly. Describe what the application is doing when you profile it — AI cannot infer workload context from numbers alone.
1. Flame Graph / Call Stack Analysis
I am profiling a [LANGUAGE/RUNTIME] application. Here is the profiler output captured while [DESCRIBE THE OPERATION — e.g., "processing a POST /checkout request under 50 concurrent users"].
[PASTE PROFILER OUTPUT — top functions by CPU time, call tree, or flame graph text]
Analyse this output:
1. Which function or call path consumes the most time?
2. Is this time in CPU work, I/O waiting, or lock contention? How can you tell?
3. What are the three highest-impact optimisation opportunities?
4. What should I NOT optimise — where would effort have little payoff?
5. What additional profiling data would help confirm your analysis?
Item 4 prevents premature optimisation of low-impact code paths. Item 5 tells you what to measure next rather than acting on incomplete information.
2. Memory Usage Analysis
My [LANGUAGE] application is experiencing [DESCRIBE SYMPTOM — e.g., "memory growing from 200 MB to 2 GB over 6 hours before OOM kill"].
Here is the heap/memory dump output:
[PASTE MEMORY PROFILER OUTPUT OR TOP ALLOCATIONS]
What are the most likely causes of this memory pattern? For each cause, describe what in the output points to it and what to check in the code.
Describe the growth pattern — is it linear, stepped, or sudden? This context changes the diagnosis significantly.
Database Query Optimisation
Always include the EXPLAIN output and your table schema. AI cannot suggest useful indexes without knowing your column types, existing indexes, and data distribution.
This SQL query is running in [DATABASE — PostgreSQL / MySQL / SQLite] and taking [DURATION] on [APPROXIMATE ROW COUNT] rows.
Query:
[PASTE QUERY]
Table schema (include column types and existing indexes):
[PASTE SCHEMA — CREATE TABLE statement or describe output]
EXPLAIN / EXPLAIN ANALYZE output:
[PASTE EXPLAIN OUTPUT]
Identify:
1. The bottleneck operation in the execution plan
2. Specific indexes to add (exact column order matters)
3. Query rewrites that would improve performance
4. Trade-offs: what does each optimisation cost (write overhead, storage, maintenance)?
If you skip the EXPLAIN output, AI will suggest generic indexes that may not match your actual execution plan. Always paste it.
Caching Strategy
I need to add caching to the following operation in a [DESCRIBE SYSTEM — e.g., "Python FastAPI service with PostgreSQL, deployed on AWS ECS"]:
Operation: [DESCRIBE WHAT THE ENDPOINT/FUNCTION DOES]
Read frequency: [e.g., "1000 reads per minute"]
Write frequency: [e.g., "5 writes per minute"]
Staleness tolerance: [e.g., "5 minutes acceptable delay between write and visible update"]
Consistency requirement: [e.g., "different users should see the same data — no user-specific caching"]
Recommend a caching strategy: where to cache (in-process / Redis / CDN / database), TTL, invalidation approach, and any cache-aside vs write-through trade-offs for my use case.
Staleness tolerance and consistency requirements are the two inputs AI needs to avoid recommending a caching approach that breaks your data guarantees.
Common Performance Issues AI Diagnoses Well
| Problem | What to provide AI | What AI gives you |
|---|---|---|
| N+1 query problem | ORM model definitions + the loop or list query code | Identifies the N+1 pattern, suggests eager loading or batching |
| Missing database index | Slow query + EXPLAIN output + table schema | Specific index creation statement with column order rationale |
| Inefficient algorithm | The function + input size context | Big-O analysis, suggests more efficient data structure or algorithm |
| Unnecessary serialisation | Hot-path code that serialises/deserialises | Identifies redundant conversions; suggests lazy evaluation |
| Blocking I/O in async code | Async function code | Spots synchronous blocking calls; suggests async alternatives |
| Suboptimal connection pool | Connection pool config + observed concurrency | Recommends pool size based on CPU count and workload type |
The Measurement Rule
AI cannot replace measurement
AI suggestions are hypotheses. Apply them only after establishing a baseline measurement. The workflow is:
- Measure: capture profiler output, query times, or request latency before any change
- Ask AI: analyse the measurement and suggest the highest-impact optimisation
- Implement: make one change at a time — not several simultaneously
- Measure again: compare before/after with the same workload
- Decide: keep, revert, or iterate based on what the numbers show
If you skip step 1 and 4, you cannot know whether an AI suggestion actually helped or whether the improvement came from something else.
Checklist: Do You Understand This?
- Why must you describe the workload context (what the app is doing) when pasting profiler output?
- What three pieces of information must you provide when asking AI to optimise a slow SQL query?
- What is the N+1 query problem, and what does AI need to identify it?
- What two parameters determine whether a caching strategy is safe for your use case?
- Explain the five-step performance tuning workflow that keeps AI suggestions as hypotheses rather than assumptions.
- Name two performance problems AI diagnoses well — and what you must always do before implementing its suggestion.