DEDUPED Hot AI repos, turned into use cases Get 5 a day

How to find the queries that eat the most database time with Pgbot

Rank statements by total time or call count to pick the first query to tune.

Who it is forA developer or DBA who knows the app is slow and needs to see which SQL statements take the most database time.

The problemA cheap query run a million times can cost more than an expensive query run twice, and the two are hard to compare by hand.

The outcomeA ranked list of statements with total time, share of total time, call count, and mean time, ready to hand to a tuning pass.

What you need first

  • A PostgreSQL server version 14 to 18 that you can reach over the network
  • A login role with the pg_monitor role and no write grants (pgbot init generates the setup SQL)
  • The pgbot binary, installed with the curl install script or npx @pgbot/cli
  • Optional: pg_stat_statements for the queries section, and hypopg on PostgreSQL 16+ for advise

Steps

  1. Confirm the pg_stat_statements extension is installed on the target, since Pgbot prints install steps if it is missing.
  2. Set the connection with export DATABASE_URL="postgres://pgbot_ro@host:5432/db".
  3. Run pgbot queries to rank statements by total execution time.
  4. Run pgbot queries --by-calls to rank by call count and compare the two lists. (inferred, check the README)
  5. Run pgbot ask "why is it slow?" for a plain-language reading of the same findings, if you have a model key set.

Prompt for your agent

Use the pgbot tool from the pgrundev/pgbot repository to run `pgbot queries --json` against <target-db-url>. Return the top five statements by total time with their share and call count. Verify that the response has enabled true and available true before you report an empty list as a real empty workload.

Caveat. Query statistics only exist when pg_stat_statements is enabled, and Pgbot reports an extension it cannot read as unavailable rather than as an empty workload.

Share: X LinkedIn Hacker News Email Copy link

Disclaimer

Deduped lists open-source repositories that belong to other people. We do not own, maintain, audit, or vouch for any of them, and a repo appearing here is not an endorsement or a safety check. The use cases, steps, and prompts are written by AI and may be incomplete, outdated, or just wrong. Where a use case is marked "Seen in the wild", we link to public posts where someone described something similar, but we have not verified those posts and the use case itself is still our AI's own write-up. Use all of this at your own risk. Only run code, commands, or agents on systems and accounts you are authorized to use, and read the code and every command before you run it, especially anything that touches credentials, files, or the network. Nothing here is professional, security, legal, or financial advice. All trademarks and project names belong to their respective owners, and their use here does not imply affiliation. Content is provided as is, without warranties of any kind. Found a mistake, a safety problem, or something you want removed? Email [email protected] and we will look at it promptly. Full disclaimer page