PostgreSQL prompts should ask for an explanation grounded in a query plan and workload. A missing index is only one possible cause of a slow request. Poor estimates, large intermediate results, lock waits or application query patterns may matter more. These prompts separate plan reading, index evaluation and contention analysis so an agent cannot turn every performance report into an untested CREATE INDEX suggestion.
Collect enough evidence to explain the plan
Supply the database version, query, table definitions, indexes and representative parameter distributions. Include an execution plan collected under conditions suitable for your environment. Do not run expensive diagnostics in production just because a prompt requests them. A planning task can begin with existing evidence and identify what a responsible next measurement would be.
The PostgreSQL EXPLAIN documentation explains planner output and notes that EXPLAIN ANALYZE executes the statement. Use that distinction when collecting evidence. Estimated cost is not a direct promise of elapsed milliseconds, so avoid asking the model to treat those numbers as a benchmark.
Read the plan before proposing changes
Ask the agent to follow rows through the plan and identify where work grows. It should separate observations from hypotheses. A large mismatch between estimated and actual rows is a clue to investigate, not automatic proof of one particular fix. Keep parameter-specific behavior visible if different inputs produce different plans.
Explain this PostgreSQL plan for {{query}}: {{plan}}.
Context: {{schema_indexes_version}} and {{parameter_distribution}}.
Walk through row flow, filtering, joins, sorting and repeated work.
Separate observed facts from hypotheses. Identify the most useful next
measurement before proposing a schema change. Do not treat every
sequential scan as an error or planner cost as elapsed time.
Evaluate an index as an ongoing tradeoff
An index serves reads while adding storage and maintenance work. Ask which query shape would benefit and whether an existing index already covers it. Include the effect on common writes and a plan for validating the build. A focused experiment should compare the same workload before and after, rather than a development table with a few rows.
Evaluate an index for {{query_family}} using {{plans_and_indexes}}.
State the selectivity and ordering assumptions. Compare proposed and
existing indexes, write overhead and storage considerations.
Give a representative benchmark and a deployment approach appropriate
for {{postgres_version}}. Return a proposal, not executed DDL.
Define evidence that would make us reject the new index.
Investigate lock waits as a timeline
A query may be fast when isolated but wait behind a long transaction. Provide sanitized activity and lock observations with timestamps. Ask which transaction holds the resource, which waits and why the transaction remains open. Do not terminate sessions as an automatic first step; establish the operational context and the consequence of interruption.
Investigate this PostgreSQL lock wait: {{timestamped_evidence}}.
Trace the blocking and waiting transactions using {{transaction_code}}.
Explain the sequence and identify unnecessarily long transaction scope.
Propose a focused application or migration change and a reproduction with
two controlled sessions. List operational actions separately, with their
consequences, instead of executing them during diagnosis.
Verify the application contract as well as speed
A faster query still needs to return the same intended rows in a stable order. Compare pagination boundaries, null handling and concurrent update behavior after a rewrite. Keep performance measurements tied to data volume, parameters and environment so reviewers understand their limits.
Save the approved PostgreSQL prompt in Kipt with blanks for plan, schema and workload. For schema rollout sequencing, use the database migration prompts, which focus on compatibility and recovery across deployments.