# Find Slow Postgres Queries in a Prisma App

Yes. An agent reads pg_stat_statements on your Postgres, traces each slow statement to the Prisma query module that sends it, and writes slow-queries.md.

[Run this on Rush](https://cloud.getrush.ai/run/investigate-slow-queries/prisma/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The run gets the database through a Project secret named `DATABASE_URL`, read as an environment variable, which should be a read-only role on a replica or a staging copy. From it the agent queries `pg_stat_statements` ordered by total execution time for the top statements, then reads the schema and the code. In an app like umami-software/umami the Prisma client is built in `src/lib/prisma.ts` with the `PrismaPg` adapter and the read-replicas extension, and most reporting queries are not Prisma builder calls but SQL strings assembled under `src/queries/sql/`, one module per report: `pageviews/getPageviewMetrics.ts` builds a `distinct on (visit_id)` subquery for entry and exit pages and joins it back to `website_event`, with filters appended by `parseFilters` on `website_id`, a `created_at` range and `event_type not in (2, 5)`. Each module dispatches through `runQuery` in `src/lib/db.ts` to either the Postgres or the ClickHouse implementation, so the agent reads the relational branch only.

Matching a normalised statement from `pg_stat_statements` to its module is pattern work: the agent takes the distinctive fragments, the `distinct on`, the `ilike` on a full path, the column a report groups by, and searches `src/queries/sql/` for the template that produces them, until each slow statement has a file and a line. Then it compares the statement's predicates with the indexes `prisma/schema.prisma` declares on `WebsiteEvent`: fourteen `@@index` lines, all leading with `website_id` and `created_at` and ending in one reporting column such as `url_path`, `referrer_domain`, `event_name` or `hostname`. A statement that filters on a column with no such index, or that sorts on one, is where `EXPLAIN` on the same shape shows a sequential scan or an unindexed sort, and that plan is the evidence the report quotes.

For each match the agent writes what it verified and what it suggests: the query as Postgres recorded it, its calls, mean and total time, the module and line, the plan, and either an index in the schema's own `@@index([...])` form or a rewrite, such as pushing a filter into the `distinct on` subquery so it runs over fewer rows. It notes, from the Prisma query-event logging that `src/lib/prisma.ts` already enables under the `umami:prisma` debug namespace, how to confirm a suggestion against the app's own log output after it ships.

The run is read-only. It runs `select` and `explain` statements only, creates nothing, and writes one file, `slow-queries.md`, which comes back as the session's artifact rather than a pull request. Every statement it ran is in the transcript.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Use the DATABASE_URL secret to read pg_stat_statements on this Prisma app's Postgres. List the top 15 statements by total execution time, find the module under src/queries that produces each by matching its distinctive SQL fragments, compare its predicates with the @@index declarations in prisma/schema.prisma, and run EXPLAIN on the same shape. Write slow-queries.md with, per statement: the recorded SQL, calls, mean and total time, the file and line, the plan, and a suggested index in the schema's form or a rewrite. List statements you could not match as unmatched. Read only: run select and explain, create no index and change no data.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is the finished session with `slow-queries.md` in its artifacts, each with a `name`, a `mime_type` and a `url` on `api.phoenix.so` that downloads without a key for an hour, and the same file through the artifacts route with the key. The report ranks the statements by total time and gives each one its recorded SQL, timing, the producing module under `src/queries/sql/` with a line, the `EXPLAIN` output, and a suggestion written as an `@@index` line for `prisma/schema.prisma` or a rewrite of the template.

A schema section records the indexes the agent verified on `website_event`, which statements were covered by one and which were not, and an unmatched section lists statements it could not trace to a module so the gap is visible rather than hidden. A reviewer checks a suggestion by opening the module, reading the predicate the plan scanned on, and deciding whether the index pays for its write cost on a table that receives every event. The transcript at GET /o/{org}/sessions/{id}/messages has every query and its answer.

Nothing changed, so nothing needs reverting. When an index ships through your migration flow, a follow-up message to the same session, POST /o/{org}/sessions/{id}/messages, asks the agent to re-read `pg_stat_statements` and add a before-and-after row for the affected statement.

## Run it on every p95 latency alert

Point the alert you already have at this run: when your monitoring reports that API latency crossed its threshold, your server sends POST /o/{org}/sessions with this body in the Project that holds the `DATABASE_URL` secret, `POST /o/{org}/p/{project}/sessions`. Each run reads `pg_stat_statements` as it stands at that moment, so the report describes the slowdown that fired the alert, not last month's. A `request_id` that is a UUID v5 of the alert id keeps a re-sent alert from starting a second run.

A webhook URL on the API key delivers `run.completed`, `run.failed` or `run.cancelled` with the session URL when the turn ends; the handler files the report with the alert. An alert storm past the key's `max_concurrent` limit is not dropped: each extra request answers 202 with status `queued` and runs as a slot frees.

## Why run it on Rush?

On a laptop this is a `psql` session against production, a stack of `EXPLAIN` outputs pasted into a document, and a search through the query modules by hand to find which one built each statement, with the trail of which plan belonged to which query lost by the second hour. In Rush the connection string is a Project secret the run reads as an environment variable, the agent keeps every statement and its answer in the transcript, and the ranked report is the result.

The report is checkable: every timing is a recorded row, every plan is quoted, every suggestion names a module and a line and is written in the schema's own index syntax. The run is read-only, so the worst a bad run produces is a wrong recommendation, never a schema change. Several databases, one per Project, can be read at once.

## Frequently asked questions

### Does the run change my database?

No. It runs `select` on `pg_stat_statements` and the catalog, and `explain` on query shapes it reads from the code. It creates no index and writes only `slow-queries.md` in the sandbox. Applying a suggestion is your migration, reviewed by you.

### How does the agent connect?

Save the connection string as a secret named `DATABASE_URL` in a Project with `POST /o/{org}/secrets`, and start the session in that Project with `POST /o/{org}/p/{project}/sessions`. The agent reads it as an environment variable; the value is never in the request body or the transcript. Give it a read-only role.

### What if pg_stat_statements is not enabled?

The report says so and stops at the schema and code review: which query modules filter on columns no index covers, from reading `src/queries/sql/` against the `@@index` lines. Enable the extension, let traffic accumulate, and start the run again with a new `request_id`.

### Will it understand queries that Prisma builds rather than raw SQL?

Yes. For a builder call the agent maps the generated statement back to the model and the `findMany` or `groupBy` that produced it, using the generated client types. In this app most reporting queries are templates, so the matching is on SQL fragments.

### Why suggest the index as an @@index line?

Because in a Prisma project the schema is the source of truth for indexes, and `prisma migrate dev` turns the line into the migration. A suggestion written in that form is one you can paste, review and migrate through your usual flow.

## More Prisma jobs

- [Upgrade a Project to Prisma 7](https://getrush.ai/run/migrate-to-prisma-7/prisma/)
- [Review Prisma Migrations in Every Pull Request](https://getrush.ai/run/review-database-migrations/prisma/)
- [Add Account Deletion to a Next.js and Prisma App](https://getrush.ai/run/add-account-deletion/prisma/)

## The same job on other stacks

- [Find Slow Database Queries in a Rails App](https://getrush.ai/run/investigate-slow-queries/rails/)
- [Find Slow Postgres Queries in a Supabase App](https://getrush.ai/run/investigate-slow-queries/supabase/)
- [Find Slow Database Queries in a Django App](https://getrush.ai/run/investigate-slow-queries/django/)

## Keep reading

- [What an agent can do in Prisma itself, once you connect it](https://getrush.ai/integrations/prisma/)
- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
