Diagnose PostgreSQL Query Bottlenecks from Execution Evidence

Required inputs

Have these details available before following the usage instructions.

• PostgreSQL version and relevant environment context, such as production, staging, read replica, or local.
• The exact SQL query or ORM-generated SQL under investigation.
• Observed symptom: latency, timeout, CPU, I/O, memory, lock wait, row count explosion, or regression window.
• Execution evidence, preferably EXPLAIN ANALYZE with BUFFERS when safe, or EXPLAIN plus runtime metrics if ANALYZE is unsafe; include the actual plan format such as TEXT, JSON, XML, or YAML.
• Relevant schema, indexes, table sizes, estimated and actual row counts, filters, joins, partitions, and constraints.
• Known recent changes: code, migrations, statistics, indexes, data volume, query parameters, configuration, or deployment events.
• Operational constraints: read/write load, maintenance windows, allowed experiment types, rollback expectations, and approval requirements.

How to use this Skill

When to use:
- A PostgreSQL query, report, job, endpoint, or dashboard has unacceptable latency or resource usage.
- You have EXPLAIN or EXPLAIN ANALYZE output, query text, schema/index details, runtime observations, or database statistics to inspect.
- You need to distinguish planner estimate problems, missing or unsuitable indexes, lock/wait issues, data-shape effects, cache conditions, or query design issues.
- You need a cautious optimization plan before approving index creation, query rewrites, configuration changes, or production rollout.

When not to use:
- Do not use for generic application performance problems when no specific PostgreSQL query or database path is implicated.
- Do not use when the user expects the assistant to directly connect to, modify, or benchmark a live database.
- Do not use as a substitute for DBA approval for production index, schema, configuration, or maintenance changes.
- Do not use when only a vague complaint is available and no query, plan, logs, metrics, or reproducible symptom can be supplied.

Instructions:
1. Use the linked source prompt AMO-P-000233 as the detailed operating checklist for PostgreSQL evidence collection and analysis; provide the required inputs above and ask the model to follow its plan/diagnosis/verification structure rather than inventing database access or results.
2. Separate supplied facts, measured evidence, assumptions, inferences, missing information, and uncertainty before recommending any remedy.
3. Map the query’s logical intent, tables, joins, filters, ordering, grouping, limits, and expected data grain before interpreting the plan.
4. Inspect execution evidence for estimate error, sequential scans, nested loop amplification, sort/hash spill risk, join order issues, missing or low-selectivity indexes, stale statistics, lock waits, parameter sensitivity, and cache-dependent conclusions.
5. Compare remedy options such as query rewrite, predicate adjustment, index change, statistics refresh, pagination change, materialization, batching, or application-level caching; state evidence for and against each.
6. Identify risks of each remedy, including write amplification, lock duration, index bloat, changed semantics, stale cached results, migration risk, and environment mismatch.
7. Define a controlled verification plan with baseline measurements, representative parameters, correctness checks, performance thresholds, rollback or drop-index path, and production monitoring requirements.
8. Require human authorization before production DDL, query deployment, configuration changes, data reprocessing, or maintenance operations.

Expected output:
A PostgreSQL performance investigation brief containing the query intent, evidence inventory, plan interpretation, ranked bottleneck hypotheses, safe remedy options, recommended next step, verification plan, rollout or rollback considerations, and unresolved evidence gaps.

Constraints and boundaries:
- Do not claim to have run EXPLAIN, changed indexes, executed queries, or inspected a database unless the user supplied that evidence from an authorized run.
- Do not recommend an index unless the supplied plan, schema, predicates, selectivity, workload, and write-cost evidence support the exact columns, order, expression, or predicate. Mark every untested index as a candidate requiring authorized non-production validation.
- Do not optimize by changing required business semantics, authorization boundaries, or data completeness.
- Treat destructive, locking, high-cost, or production database actions as requiring explicit human review and approval.
- Avoid exposing sensitive query data; request redaction or aggregation when data contains personal, confidential, or regulated information.

Powered by an Amo.ng Prompt

PostgreSQL Slow Query Evidence Pack

Open the linked prompt to use the instructions that power this Skill.

Completion criteria

Complete when:
- Every bottleneck hypothesis cites specific plan nodes, runtime measurements, row estimates, schema facts, lock evidence, or explicitly marked assumptions.
- Recommended remedies are bounded and include correctness checks, performance acceptance criteria, and rollback or recovery notes.
- The output distinguishes confirmed findings from plausible but unverified causes.
- The verification plan can be performed by an authorized operator without relying on unstated tool access.
- Production-impacting actions are clearly separated from analysis and marked for human approval.