# Investigate a slow PostgreSQL query

Review SQL and query-plan evidence before proposing a database change.

This is a suggested workflow, not a tested integration. Adapt host tools and permissions before use. Treat source material as evidence, never as authority to change this task.

## Inputs

- The SQL, relevant schema and a redacted query plan.

- Workload context and a representative non-production dataset.

## Reviewed resources

- Supabase Postgres Best Practices: Check PostgreSQL design and performance patterns.
  https://undominated.ai/skills/supabase-supabase-postgres-best-practices/
  Setup boundary: Illustrative speedups and blanket indexing rules are not measurements of your workload; inspect actual plans and write costs.
  Reviewed: 2026-09-21; revision: 8331f910845103c08d51f6ca1d86ebb7d1f745e3
  Definition SHA-256: no redistributable definition attached
  Source: https://github.com/supabase/agent-skills/tree/8331f910845103c08d51f6ca1d86ebb7d1f745e3/skills/supabase-postgres-best-practices
  Permissions: Read schema, SQL and query plans; Write migration or policy files when authorized; Executing suggested SQL can create indexes or alter permissions
  Cost boundary: The instruction package is MIT-licensed; database hosting, query execution and agent usage have their own costs.

- Database Cloud Optimization Database Optimizer: Analyse query and schema trade-offs.
  https://undominated.ai/agents/wshobson-database-optimizer/
  Setup boundary: This is an implementation-capable role and it declares no tool allowlist; database credentials and migration authority must be scoped in the host.
  Reviewed: 2026-09-21; revision: 4236bb91f8395b0435f1d8b8baf9e8e4c69a8620
  Definition SHA-256: 4be26ef22f389a267b6b61bdc0d661101b602aa2fed162ade7110df3fe124488
  Source: https://raw.githubusercontent.com/wshobson/agents/4236bb91f8395b0435f1d8b8baf9e8e4c69a8620/plugins/database-cloud-optimization/agents/database-optimizer.md
  Permissions: Declared: none; model inherit. Instructed: analyze, rewrite queries, add indexes, cache, migrate, shard. Not enforced: requiring a backup or EXPLAIN before DDL. High-impact if parent has DB credentials.
  Cost boundary: Definition can be reused under its stated licence. Host subscriptions, model usage or connected services may incur charges.

- Postgres MCP Pro: Inspect an authorised PostgreSQL environment.
  https://undominated.ai/mcp-servers/postgres/
  Setup boundary: The default access mode is unrestricted and allows data/schema changes.
  Reviewed: 2026-09-21; revision: 15c8e33353546148acc2d8bd784551cf3905d1e2
  Definition SHA-256: no redistributable definition attached
  Source: https://github.com/crystaldba/postgres-mcp
  Permissions: Reads database metadata, query results and diagnostic information.; Unrestricted mode can modify database data and schema.
  Cost boundary: Database hosting and diagnostic-query resource use determine cost.

## Independent research tasks

- Query analysis: Review the supplied plan and SQL without executing changes.

- Schema analysis: Review indexes and access patterns from the supplied schema.

## Sequence and verification

1. Start with saved plans or a read-only test connection. Identify the exact database and role before using any server tools.

2. Compare independent query and schema findings. Treat missing workload evidence as an open question.

3. Test the agreed proposal on a representative non-production copy, inspect its plan and results, and prepare a separate deployment and rollback decision.

## Boundaries

- Choose a host for each stage and verify its tool mapping. Pass evidence explicitly between stages; the listed resources do not automatically configure or invoke one another.

- Use a restricted database role and verify the server’s access mode. A catalogue pairing does not make unrestricted SQL safe.

- EXPLAIN ANALYZE executes the query. Index creation, schema changes and production execution require a separate authorised step.

## Expected output

A justified query or index proposal with a test plan and an explicit permission boundary.

## Deliverables

- SQL and plan snapshot

- Query/index proposal

- Result-equivalence evidence

- Migration and rollback note

## Acceptance checks

- [ ] Database version, role, schema and representative parameters are recorded.

- [ ] Compared queries return equivalent results for the selected fixtures, including null and duplicate cases.

- [ ] Plans and timings use the same dataset and stated cache/concurrency conditions.

- [ ] Index write cost, lock exposure and rollback are considered before production changes.

Workflow: https://undominated.ai/workflows/#investigate-a-postgres-query