Explain one statement, establish why it is slow, and propose a fix that is verified rather than guessed.
promptread-only tools
explain-and-fix(sql, [params], [schema])The 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.
Explain this statement and propose a fix. SQL: SELECT id, status FROM shop.orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20 Parameters: [88213] Establish what is actually slow before proposing anything. A plan is evidence, not a verdict: a statement can be slow with a perfect plan because the data is cold, the table is bloated, or it spent its time waiting rather than working. Say which of those it is. 0. Call checkPrivileges. EXPLAIN needs SELECT on every table the statement touches, so a restricted role fails here rather than returning a worse plan, and evaluateIndex in step 7 needs hypopg. 1. Call explainQuery with this sql and these params. Leave analyze false first: the plan alone usually shows the problem, and analyze really executes the statement. Parameters were supplied, so this is planned with those real values rather than generically. Run it once WITHOUT them as well and compare: if the generic plan differs, the application may be getting that one instead, and the statement is only slow when prepared. Skewed columns are where the two diverge, and step 4 is where you check for that. Read the generic and analyzed flags the tool returns rather than assuming which plan you got. Then read the plan's Settings block, and treat it as a second thing that can make the whole answer wrong. It names the settings that differ from the built-in default IN THIS SESSION, and this session is not the one the statement runs in. work_mem alone changes the algorithm rather than the cost -- the same statement plans as a HashAggregate with a large one and as a Sort plus GroupAggregate with a small one -- so node types, Sort Method and anything that spilled are all read off the wrong plan when they disagree. Call hostCapacity and compare Settings against overrides, which carries the per-role and per-database values pg_settings cannot show. If the executing role has its own work_mem, do not stop at saying the plan is wrong -- plan it again with plan_as_role set to that role. explainQuery applies the role's planner settings for that one transaction and returns planning_environment saying exactly what it applied and what it skipped. THE COMPARISON IS THE FINDING, and it is the same shape as the generic-versus-custom comparison above: two plans, one difference, and the difference is the answer. Where they agree, the environment is not the cause and you can rule it out instead of carrying it as a caveat. Read skipped_from_role rather than assuming the role was fully reproduced. A search_path or a statement_timeout on that role is reported there and NOT applied -- search_path deliberately, because it changes which objects the statement resolves to rather than how they are joined, and planning against a different table than you meant is worse than not planning at all. 2. If the plan alone is not conclusive and the statement only reads, call again with analyze true AND a timeout_ms -- analyze is refused without one. It runs EXPLAIN (ANALYZE, BUFFERS) and is honoured only after the plan is proven free of any ModifyTable node, so a data-modifying statement can never be executed here. Say that you are about to run it. With explicit settings it runs only within the execution budget the connection's declared host capacity allows; if analyzed comes back false, read planning_environment.execution_budget for why, and either lower the settings or drop them -- plan_as_role is not budgeted, because it is production's own environment. For an INSERT, UPDATE, DELETE or MERGE this step is unavailable by design, so the estimates are all you get. Reason from them and say so, rather than presenting an estimate as a measurement. 3. Read the numbers in this order, because they answer different questions and the first one that answers yours ends the search. - Estimated against actual rows, per node. A ratio past roughly 100x is the planner being misinformed, and no index built on a wrong estimate will be chosen. - Buffers, which analyze returns. shared_hit is memory and shared_read is disk: a plan that looks fine but reads heavily from disk is a cold cache or a working set larger than shared_buffers, and that is a capacity answer rather than a query one. Large temp blocks are a sort or hash that exceeded work_mem. - Heap Fetches on an Index Only Scan. Nonzero means the visibility map is stale and the scan is going to the heap anyway; the fix is vacuum, not an index. - Time not accounted for by any node. If the nodes are fast and the statement is not, it waited -- for a lock, or for I/O. That is triage-lock-contention or the buffers above, and neither is fixed by rewriting the query. 4. Ground the plan in what the tables actually are, before believing anything the plan implies. Call tableStats for each table involved. - rows first. A sequential scan of a few thousand rows is the correct plan and needs no fix; proposing an index there is noise. - last_analyze and last_autoanalyze beside n_mod_since_analyze: stale statistics explain more bad plans than missing indexes do, and the two timestamps say whether anything is analyzing this table at all. - most_common_vals, n_distinct and histogram_bounds for any column in a predicate. This is the empirical answer to whether the column is skewed, and skew is what makes one plan right for a common value and wrong for a rare one -- the same thing that makes a generic plan dangerous. Then use them, rather than only reading them. Both carry values that actually occur in the column, so they are usable directly as parameters: most_common_vals[0] is the most frequent value the table holds, and histogram_bounds low, mid and high are the observed minimum, median and maximum. Re-plan the statement with each in turn and compare. A plan that is identical across all of them is stable and the parameters are not the problem; a plan that flips between a common value and an extreme one is parameter-sensitive, and that is the finding -- an index chosen for one end of the distribution will be wrong at the other, and the generic plan is wrong for both. This is the one test that needs no invention. A constant you made up may match no rows and produce a plan for a case that never happens; these values are what is actually there. Where histogram_bounds is null the column has no histogram at all -- every value is in the MCV list, or nothing has analyzed it -- and which of those it is comes from the analyze timestamps above. Three points settle whether the plan is stable. When they are not enough -- the plan flips somewhere between them and you need to know where, or the estimate is wrong in a way the extremes do not explain -- call columnHistogram for that one column. It returns every bound, and they are equal-frequency, so consecutive entries bunched close together are a dense region of the distribution and a wide gap is a sparse one. Sampling across the buckets rather than at the ends is how you find where the plan actually turns. If a scan reads far more heap than it returns rows and the statistics are current, suspect bloat: tableBloat measures the table and indexBloat one index, and bloat-and-vacuum-review is the fuller investigation. 5. Classify the fix before writing one, and say which it is. Either the statement asks for something the planner cannot use -- a predicate that is not sargable, a function or 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 -- call listExtendedStatistics first, because an object that exists and is not helping is a different problem from one that was never created, and only the second is fixed by CREATE STATISTICS. There is a third state between those two: built false means the object was declared and never ANALYZEd, so it holds no data and changes no plan. That reads as a fix already in place and is really one never completed, and the answer to it is ANALYZE. built_kinds says which of the declared kinds were actually computed. Or the plan is right but under-resourced, and the fix is configuration such as work_mem for a node that spilled. Or nothing supports the access path the statement needs -- and only then is the fix DDL. A fifth outcome is legitimate and often correct: the statement is already as fast as it can be, and the cost is inherent to the work it does. Say so plainly when it is true. Proposing a fix for a statement that does not need one is worse than proposing nothing. 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. 6. If the answer is an index, choose its shape deliberately and justify each part -- an index is not a single decision. - Call duplicateIndexes first. An index that is a prefix of one that already exists buys nothing and costs writes forever, and this is the most common wasted proposal. - Column order: equality predicates first, then the range or inequality, then anything used only for ordering. A composite index is usable only up to its first range column. - INCLUDE for columns the statement returns but does not filter on, which is what buys an index-only scan without widening the key. - A WHERE clause on the index itself when the statement always carries the same selective constant: a partial index is smaller and cheaper to maintain. - The operator class where it is not the default -- text pattern matching, trigram search and jsonb containment each need one, and the index is simply not used without it. 7. Verify what can be verified, here, before recommending it. - A rewrite is fully checkable: call explainQuery on the rewritten statement and show that the plan changed, and how. Same params, so the comparison is honest. - An index is checkable wherever hypopg is installed: call evaluateIndex with the CREATE INDEX statement and report whether the planner actually took it. 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 must be presented as one rather than as a result. - Compare like with like. A lower cost estimate is not a result; what supports the claim is the node that disappeared, the row count that stopped being wrong, or the buffers that stopped being read. 8. Report the chain: what the statement does, which reading showed the cause, the fix, and the evidence it works. State how anyone would confirm it afterwards -- for a statement that came from pg_stat_statements, mean_exec_time on the same queryid is the measurement, and it needs a reset or a before-figure to mean anything. Hand any DDL to plan-schema-change rather than giving a CREATE INDEX to run: whether an index is correct and whether it is safe to build on this table are different questions, and evaluateIndex answers only the first.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "explain-and-fix",
"arguments": {
"sql": "SELECT id, status FROM shop.orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20",
"params": "[88213]",
"schema": "shop"
}
}
}