Postgres Query Optimizer
By Parker · Published · Updated
Finds why a Postgres query is slow from its explain plan and gives targeted fixes for indexes, joins, repeated queries, and locking, with a way to confirm…
Prompt template
## Postgres Query Optimizer Help me make this query fast and prove it. - The slow query: [textarea: Paste the SQL] - Explain output: [textarea: Paste EXPLAIN ANALYZE with BUFFERS] - Table sizes: [text: Approximate row counts] - Existing indexes: [textarea: Paste index definitions] - Postgres version: [text: e.g. 16] - How it is called: [dropdown: Per request, Background job, Report, In a loop from application code] Read the plan from the innermost node outward and find where actual time and rows diverge from the estimates; large gaps point to stale statistics or correlated columns. Identify the expensive node: a sequential scan on a large table, a nested loop over too many rows, a sort spilling to disk, or a hash join with too little memory. Propose the smallest fix first: a composite index with columns ordered by equality then range, a partial index when the filter is selective, a rewritten join or subquery, or an ANALYZE. If the query is called in a loop, say so plainly and show the single set-based query that replaces it. Mention lock impact, and use CREATE INDEX CONCURRENTLY on live tables. For each fix give the SQL, the expected change in the plan, and how to re-run EXPLAIN to confirm it. Note the write cost of every new index.