---
name: postgres-query-optimizer
description: "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 each gain. Use when a Postgres query is slow and needs its EXPLAIN ANALYZE plan read, with index, join, or N+1 fixes that can be verified. Do not use for schema design from scratch or tuning server-level configuration. Related topics: data, coding."
---

# Postgres Query Optimizer

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 each gain.

## When to use

Use this skill when a Postgres query is slow and needs its EXPLAIN ANALYZE plan read, with index, join, or N+1 fixes that can be verified. Do not use it for schema design from scratch or tuning server-level configuration.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Read the plan from the innermost node outward and mark where estimated and actual rows or time diverge.
3. Name the most expensive node and the cause, such as a sequential scan, a spilling sort, or a nested loop over too many rows.
4. Propose the smallest fix first, with the exact SQL for the index or the rewritten query.
5. Check whether the query runs inside an application loop and, if so, give the single set-based replacement.
6. State the expected plan change, how to re-run EXPLAIN to confirm it, and the write cost and lock impact of each index.

## Inputs

- **The slow query** (longer free text)
- **Explain output** (longer free text)
- **Table sizes** (free text, e.g. "Approximate row counts")
- **Existing indexes** (longer free text)
- **Postgres version** (free text, e.g. "16")
- **How it is called** (choose one: Per request, Background job, Report, In a loop from application code)

## Output

A diagnosis of the slow node, then each fix with its SQL, expected plan change, and confirmation step, ordered smallest change first.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Postgres Query Optimizer

Help me make this query fast and prove it.

- The slow query: {{The slow query}}
- Explain output: {{Explain output}}
- Table sizes: {{Table sizes}}
- Existing indexes: {{Existing indexes}}
- Postgres version: {{Postgres version}}
- How it is called: {{How it is called}}

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.

## Pitfalls

- Adding an index on every filtered column, which slows writes and still misses the column order the planner needs.
- Creating an index without CONCURRENTLY on a busy table and blocking writes.
- Tuning from EXPLAIN without ANALYZE, so the estimated cost hides the real runtime.