# Find Slow Database Queries in an Elixir App Yes. An agent runs your Elixir app on seeded Postgres, logs every query over 50 ms and reports the Ecto code behind each with EXPLAIN ANALYZE and a fix. [Run this on Rush](https://cloud.getrush.ai/run/investigate-slow-queries/elixir/), in a Project that has your repository. ## What the agent does The agent clones the repository into its own cloud sandbox, installs Elixir and Postgres, and brings the app up. In an Elixir app like hexpm, the Phoenix app behind hex.pm, it reads `mix.exs` (Elixir `~> 1.20`, `ecto_sql`, `postgrex`, `oban ~> 2.23`) and the `ecto.setup` alias, which resets the database and runs `priv/repo/seeds.exs`. The schema is loaded from `priv/repo/structure.sql`, so the agent starts from exactly the tables, materialized views and indexes production has, including `package_downloads` and `release_downloads`, which are materialized views. The repository's seeds are a starting set, not a load test, so the agent writes a seeding script that uses the app's own schemas to create tens of thousands of packages with many releases each, dependencies between them so `package_dependants` has rows, and download counts, and then refreshes the materialized views. It sets Postgres `log_min_duration_statement = 50`, enables `pg_stat_statements`, and exercises the main pages: the package list sorted by name, recent and downloads, a search, a package page and its dependants. For each slow query it finds the Ecto code that builds it. In hexpm most package queries are composed in `lib/hexpm/repository/package.ex`: the downloads-sorted list pages ids through `downloads_join` and `Hexpm.Utils.paginate` in a subquery before joining back to `Package`, dependants use `dependant_ids_query` and `dependency_exists_query` over `PackageDependant`, ordered by `downloads DESC NULLS LAST`, and search goes through `like` and `fragment` clauses over `name` and `meta`. The agent reads the schema's indexes beside each one, such as `packages_name_trgm` (a `gin_trgm_ops` index), `package_dependants_dependency_id_dependant_repository_id_index` with `INCLUDE (package_id)` and `package_downloads_view_downloads_package_id_idx`, and explains why the planner used or skipped them. It runs `EXPLAIN (ANALYZE, BUFFERS)` on each slow query, then tries a fix on the seeded data: an index created in the sandbox, a rewritten Ecto query, a `preload` changed to a join or the reverse, or a narrower `select`. It measures each change on the same data and keeps both times. It also counts repeated queries per page to catch preloads issued per row. The repository is left as it was. ## Start it with one API call `POST https://api.prix.dev/o/{org}/sessions` ```json { "agent": "claude", "input": "Load this app in the sandbox with Postgres, the schema from priv/repo/structure.sql and a realistic amount of seed data (tens of thousands of packages with releases, dependencies and download counts; refresh the materialized views). Set log_min_duration_statement to 50 ms, exercise its main pages, and write slow-queries.md: each query over 50 ms, the Ecto code that builds it with file and line, its EXPLAIN ANALYZE, the indexes the planner used or skipped, and the index or rewrite you tried with the time before and after on the same data. Do not open a pull request.", "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.prix.dev/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 `slow-queries.md` as an artifact on the session, which the session read lists with a download link. It starts with the seeded row counts and the pages exercised, then gives each slow query its own section: the SQL as Postgres logged it, the Ecto function that composes it with file and line, the plan, the indexes involved, and the change that was tried with its measured time before and after. Queries that are fast alone but repeated per row are grouped separately, with the page and the preload that causes them. The session's transcript keeps the seeding script, every `mix` and `psql` command, the log excerpts and every plan, so any number in the report can be reproduced by rerunning the same step. Before taking a change, a reviewer checks the seed data's shape against production, weighs a new index against write cost on tables the publish path writes, and checks that a rewritten query returns the same rows. A follow-up message to the same session can then ask the agent to add the migration and the query change and open a pull request. ## Run it on every schema migration merged Query plans change when the schema does, so run it when a migration lands. A CI job that sees a new file under `priv/repo/migrations` can send this request, and the report says whether any page got slower before the change ships. Set a webhook URL on the API key and Rush POSTs `run.completed` when the turn finishes, with the `session_url`. Your handler takes `slow-queries.md` from the session's artifacts and posts it on the merged pull request for the author to read. ## Why run it on Rush? Profiling Ecto queries needs the real schema, a lot of data and a running app. Doing that on a laptop means a local Postgres with a large seed, refreshed materialized views and changed logging settings that then have to be undone. In a Rush sandbox the agent sets all of it up on its own machine and discards it when the run ends. The report carries a plan and a measured before and after for every finding, and the transcript keeps the commands that produced them. Separate apps or separate parts of one app can be profiled at the same time, one run each, up to the key's concurrent run limit. ## Frequently asked questions ### Does it use my production data? No. It loads your schema from `priv/repo/structure.sql` and seeds its own data in the sandbox. If you want it to start from your real slow queries, attach a `pg_stat_statements` export to the request. ### Does it understand Ecto composition? Yes. It follows the query from the controller through the context functions that add `where`, `join`, `order_by` and `preload` clauses, so the report points at the function that adds the slow part, not only at the final SQL. ### What about materialized views? The agent refreshes them after seeding, because a list sorted by downloads reads `package_downloads`, and an empty view would hide the cost. The report says when each was refreshed. ### Will it change my code? Not in this run. It measures changes in the sandbox and reports them. A follow-up message to the same session can ask for the migration and the query change in a pull request.