postgres-query-review

Review a PostgreSQL query against its real execution plan and say what to change, with the evidence for each suggestion.

Arguments

• A query (required; ask for it when missing): the SQL text, a file, or the place in the code that builds it.
• Typical parameter values (optional): without them, ask for realistic ones or take them from the data.

Flow

1. Get the exact SQL the database receives. For a query built by an ORM or a query builder, obtain the generated statement (its log or its "to SQL" call) rather than guessing it.
2. Read the schema of every table involved through the dbhub MCP server: columns and types, indexes, constraints, estimated row counts and, for the filtered and joined columns, how selective they are.
3. Get the plan. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for a reading query, with realistic parameters. Follow the "Postgres" rules: plain EXPLAIN only for a statement that writes, and ask before ANALYZE on a database that may be production. Without database access, say so and review from the SQL and the schema files alone, marking every conclusion as unverified.
4. Read the plan from the most expensive node:
   • estimated rows far from actual rows (stale statistics, correlated columns, a function over a column);
   • a sequential scan that reads many rows to return few;
   • a nested loop over many rows, or a hash or sort that spills to disk;
   • rows removed by a filter after an index scan (the index does not cover the condition);
   • a sort that an index in the right order would avoid;
   • high planning time, or many partitions scanned where one was meant.
5. Propose changes in order of effect and cost, following the supabase-postgres-best-practices skill: rewrite the query first (a predicate the index can use, EXISTS instead of a join that multiplies rows, keyset pagination instead of a large OFFSET, fewer columns), then an index (give the exact CREATE INDEX CONCURRENTLY statement and say what it costs on writes), then statistics or configuration.
6. Verify what can be verified without writing: re-run the plan for a rewritten query and compare. An index suggestion stays unverified unless the user creates it; never create it yourself.
7. Check the code around the query for the same query run in a loop (one query per row of another result).

Report

• The query, the database it was measured on, and the plan's total time and buffers.
• The cause of the cost, quoting the plan lines that show it.
• Suggestions, best first: the change, the expected effect, the cost or risk, and whether it was verified, with the before and after times.
• What could not be checked, and what to measure on production-sized data.