When slow becomes user-visible
Picture the moment a dashboard page times out while you are SSH’d into the machine, staring at EXPLAIN ANALYZE and trying not to guess. In that moment, AI should help you collect evidence, not invent a new index from thin air.
What to gather before the analysis
Start with facts instead of fixes. You need schema shape, row counts, existing indexes, the exact query text, and a clear picture of the real workload. Without that context, any optimization advice is only a guess.
Where AI is actually useful
A well-scoped prompt can make the model walk through the execution plan node by node, explain which node is expensive, and suggest safe follow-up checks. That is useful when the query is hurting an API or dashboard and you want to understand the mechanism before touching the database.
Common mistakes
- Treating the slowest node in the plan as the root cause without checking the full query path.
- Forgetting
ANALYZEand relying only on an estimated plan. - Recommending an index without checking selectivity and real query frequency.
- Ignoring data distribution, filter values, and how the query is actually called from code.
When to involve a DBA
If the fix would require schema changes, the plan is still unclear, or the query sits on a critical path with heavy write load, stop and bring in the right owner. At that point you need permissions, context, and sometimes a DBA rather than another round of guesswork.
Next step
Take this template and run it against one slow query that is already hurting your team. Collect the facts first, read the plan second, and only then decide whether the database needs a change at all.
Quick checklist
- Gather the exact SQL query, database engine, and version.
- Record table sizes, row counts, and existing indexes for every table in the query.
- Run EXPLAIN or EXPLAIN ANALYZE in a safe environment and keep the raw output.
- Note the real workload: filters, typical parameter values, peak traffic, and the code path that calls the query.
- Check whether the slow part is a join, sort, scan, or missing filter.
Diagnose a slow SQL query without risky changes
You are helping diagnose a slow SQL query safely. First ask me for: - the database engine and version; - the exact SQL query; - the table schema and relationships; - row counts for the important tables; - existing indexes; - the current execution plan or EXPLAIN ANALYZE; - the real workload pattern: filters, parameter values, run frequency, and peak traffic. Then explain where the time is likely going in plain language. Do not recommend schema changes, new indexes, migrations, or production DDL until the evidence supports them. If the plan is missing, tell me the safest next check to run first. If I share EXPLAIN ANALYZE, walk through it node by node and point to the section that is consuming the time. Finish with a short list of safe next actions and the exact signal that would justify each one.