Trace a slow statement from pg_stat_statements to a plan and a fix.
promptread-only tools
diagnose-slow-query([query_id], [min_duration_s])statementStats; omit to start from the worst offenderThe text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.
Diagnose a slow statement on this PostgreSQL server. 0. Call checkPrivileges first. statementStats hides the query text of other roles without stats access, explainQuery needs SELECT on the tables referenced, and tableBloat needs a grant of its own -- a plan built on tools that will not answer wastes the incident. 1. Call statementStats to rank statements by total execution time. Read info.dealloc first: if it is non-zero the extension has been evicting entries, so the list is the slowest of what survived, not the slowest overall -- say so before drawing conclusions. 2. The statement of interest is query_id 3301928477102934411. Call explainQuery with that queryid. 3. Read the plan for the usual causes in this order: a sequential scan on a large table, an estimate that is orders of magnitude off the actual row count, a sort or hash that spilled to disk, and a nested loop driven by a bad estimate. The spill you can confirm without the plan: statementStats already returned temp_blks_read and temp_blks_written for this statement in step 1, and a non-zero write count is the sort or hash going to disk, every time it ran rather than the once EXPLAIN ANALYZE watched. Use the plan to find which node, and the counter to know it is typical. Before concluding anything from a spill, read the plan's Settings block. It lists what differs from the built-in default in THIS session, and the statement does not run in this session. work_mem decides whether a sort or hash spills at all, so a spill visible here and absent in production -- or the reverse -- is a difference in environment rather than in the statement. hostCapacity.overrides carries the per-role and per-database values pg_settings cannot show. Where the executing role has its own work_mem, plan it again with plan_as_role set to that role rather than only noting the difference: work_mem decides whether a sort spills at all, so a spill that appears under one environment and not the other is the whole diagnosis, and a spill that appears under both is real. planning_environment reports what was applied and what was skipped, so read skipped_from_role before treating the reproduction as complete. 4. If the estimates are wrong, call tableStats on the tables involved and compare n_mod_since_analyze against last_analyze and last_autoanalyze -- stale statistics explain more bad plans than missing indexes do, and those two timestamps also say whether anything is analyzing the table at all or somebody is doing it by hand. If the statistics are current and the estimate is still wrong, the column is probably skewed rather than stale, and that is a different fix. most_common_vals and histogram_bounds in the same payload are the evidence: a predicate on a value in the MCV list is estimated from its recorded frequency, while one outside it is estimated from the histogram, and a planner that is right for the first and wrong for the second is describing skew rather than staleness. Call columnHistogram on that column when the shape of the distribution is what you need, and consider a higher statistics target -- it reports the one in effect -- or extended statistics where two columns are correlated. 5. If the plan shows a sequential scan where a usable index exists, or an index scan fetching far more heap pages than it returns rows, suspect bloat rather than the plan. tableBloat measures dead space in the table, indexBloat in one index; both cost a scan, so name the object rather than sweeping the schema. 6. If bloat is confirmed, the fix is usually vacuum reaching the table more often rather than a REINDEX. Read n_dead_tup and the last vacuum times from tableStats and the per-table autovacuum settings from tableDetails.reloptions, and follow bloat-and-vacuum-review before changing anything cluster-wide -- an unconsumed replication slot makes every autovacuum setting irrelevant, and that prompt checks for one. 7. Before proposing an index, call duplicateIndexes to check that one does not already exist, and tableDetails to see what is there. If the change is DDL, follow plan-schema-change: an index that is correct and an index that is safe to create on a live server are different questions. 8. Decide what kind of fix this is before writing one, and say which. Either the statement asks for something the planner cannot use -- a predicate that is not sargable, a function or a cast over an indexed column, NOT IN against a nullable subquery, OFFSET deep into a large result -- and the fix is a rewrite. Or the planner was misinformed, and the fix is statistics: an ANALYZE, a higher statistics target, or extended statistics for correlated columns -- and before proposing those, call listExtendedStatistics, because a statistics object that already exists and is not being used is a different problem from one that was never created. Read `built` rather than mere presence: an object that has never been ANALYZEd has a catalog row, no data and no effect on any plan, so it reads as a fix already tried and is really one never finished -- and there the answer is ANALYZE, not another CREATE STATISTICS. Or the plan is right but under-resourced, and the fix is configuration, such as work_mem for a node that spilled. Or the statement is already well formed and nothing supports the access path it needs -- and only then is the fix DDL. Prefer them in that order, because that is the order of what they cost. An ANALYZE is free and instant. A rewrite costs a deploy and nothing in the database. Configuration changes the behaviour of every other query too. An index is a write cost paid by every INSERT and UPDATE for as long as it exists, to buy speed for one read pattern. Say why the cheaper options were rejected rather than passing over them. 9. Verify what can be verified. A rewrite can be checked here and now: call explainQuery on the rewritten statement and show that the plan actually changed, and how. An index is checkable too wherever hypopg is installed: call evaluateIndex with the CREATE INDEX statement and report whether the planner actually took it, because a proposed index the planner ignores is the common case and a cost figure alone hides it. checkPrivileges says whether hypopg is there; where it is not, an index is a prediction and has to be presented as one rather than as a result. Either way hand the creation to plan-schema-change: whether an index is correct and whether it is safe to build on this server are different questions, and evaluateIndex answers only the first.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "diagnose-slow-query",
"arguments": {
"query_id": "3301928477102934411"
}
}
}