Profile Postgres Queries
Profile a Postgres workload to find the queries actually costing you — rank by total time with pg_stat_statements, EXPLAIN the worst offenders, and recommend the highest-leverage fix.
/profile-postgres-queries<database/connection details, a slow endpoint, or a description of the workload>Install to ~/.claude/commands/profile-postgres-queries.md
Scope
Treat $ARGUMENTS as the workload to profile — a database/connection, a slow endpoint or report, or a description of where the database feels slow. The job here is triage: find which queries cost the most before optimizing any one of them, so effort goes where it pays.
NOTE
This command profiles a workload to rank its worst queries. To then fix a single slow query from its plan, hand off to the sql-optimizer skill; to choose the right index for it, the postgres-index-strategist.
Step 1 — Establish the data source
Prefer pg_stat_statements (the aggregated view of normalized query stats) — confirm the extension is enabled. If it isn't available, fall back to the slow-query log or a representative trace, and say so. Profiling against an empty dev database tells you nothing; use representative data and traffic.
Step 2 — Rank by total cost, not just slowness
Pull the top queries by total_exec_time (total time spent across all calls) — the real cost driver — alongside calls, mean_exec_time, and rows. A fast query run a million times can outweigh a slow one run twice. Report the top offenders by total time and by call count.
Step 3 — EXPLAIN the worst offenders
For each top query, run EXPLAIN (ANALYZE, BUFFERS) on a representative instance and read for the dominant cost: sequential scans on large filtered tables, estimate-vs-actual row blowups (stale statistics), nested loops over huge intermediates, or sorts spilling to disk.
Step 4 — Classify the fix
For each, name the highest-leverage fix and route it:
- Missing/wrong index → an index recommendation (type matters — B-Tree vs. GIN vs. BRIN; see postgres-index-strategist and Indexing Postgres at Scale).
- Stale statistics →
ANALYZEthe table before anything else. - A single slow query needing a rewrite → sql-optimizer.
- App-side N+1 (same query, huge
calls) → fix in the application (eager-load / batch), not the database.
Step 5 — Report a prioritized plan
Produce a ranked table — query | total time | calls | mean | the diagnosis | the proposed fix — ordered by total cost so the team fixes the biggest win first. Quantify where you can ("this one query is 40% of total DB time").
WARNING
Optimize by total time, not by the single slowest query. The query that dominates your database's load is often a moderately-fast one executed constantly — chasing the one query with the worst single-run time can spend effort where it barely moves the needle.
Related
- Indexing Postgres at Scale: B-Tree vs GIN vs BRIN and the Hidden Cost of Over-IndexingA practical guide to choosing Postgres index types — B-Tree, GIN, BRIN, partial, and covering — and why every index you add taxes every write.
- Postgres Index StrategistRecommend the right Postgres index for a query or workload — choosing B-Tree vs. GIN vs. BRIN vs. partial/covering/expression, checking for redundant or unused indexes, and verifying the choice against the query plan. Use when a query needs an index, when deciding an index type for jsonb/array/full-text/time-series data, or when auditing an over-indexed table.
- SQL OptimizerDiagnose a slow SQL query from its execution plan and propose a verified optimization — finding the real bottleneck (sequential scan, missing or unused index, bad join order, app-side N+1) and measuring the fix before and after. Use when a query is slow and you need a fix backed by EXPLAIN ANALYZE, not a guess.
- Performance EngineerUse this agent to profile and optimize performance — latency, throughput, memory, bundle size. Examples — a slow endpoint, an N+1 query, a heavy render, a large JS bundle.
- DB MigrateGenerate and apply a database migration the safe way — using the project's migration tool, with expand-contract discipline for breaking changes, lock-free DDL, and a reversible up/down.