How to Use the EXPLAIN ANALYZE Reviewer Prompt to Find the One Node That Makes a Query Slow

A slow PostgreSQL query usually has one main problem, but the plan that explains it can run to dozens of lines of nested nodes, costs, and buffer counts. Reading it well is a skill, and many application developers only need it a few times a year. The PostgreSQL EXPLAIN ANALYZE Slow Query Reviewer: Plan Node Walkthrough, Row Estimate Misses, Buffer Reads, Index and Rewrite Options, and a Safe Rollout prompt walks through the plan node by node, names the bottleneck, and proposes fixes you can test without putting production at risk.

What the prompt produces

1. A plan walkthrough from the innermost node out, in plain words.
2. A row estimate table comparing estimated and actual rows, with likely causes for big misses.
3. A buffers reading that explains cache hits, disk reads, and temp files from sorts or hashes.
4. The main bottleneck in one sentence tied to a specific node.
5. Index options with column order, partial and covering indexes, and when no index will help.
6. Query rewrite options with the rewritten SQL.
7. Statistics fixes such as ANALYZE, a higher statistics target, or extended statistics.
8. A safe rollout plan built around building indexes concurrently and re-running the plan.

How to fill the inputs

SlowQuery should be the exact SQL the application sends, with real parameter values. A query that is fast for a small customer can be slow for the largest one, so pick values that reproduce the problem.

PlanOutput must come from EXPLAIN (ANALYZE, BUFFERS), not plain EXPLAIN. Plain EXPLAIN only shows estimates. ANALYZE runs the query and shows what actually happened, and BUFFERS shows where the data came from. Run it on a replica or staging copy if the query writes data or is very heavy.

TablesAndIndexes gives the table definitions, existing indexes, and rough row counts. Index advice is only useful if the prompt knows what already exists.

VersionAndSettings includes the PostgreSQL version, work_mem, and when the table was last analyzed. Stale statistics are a common cause of bad plans.

WorkloadTarget says how often the query runs and how fast it needs to be, so the advice is sized to the actual need.

Reading the example output

The example is an order history page for the largest tenant in a multi tenant app.

• The walkthrough shows a bitmap scan pulling over 200,000 rows, then a sort on disk, all to return 50 rows.
• The row estimate table shows the planner guessed about 12 times too low, pointing to skewed tenant sizes and stale statistics.
• The buffers reading explains that almost every page came from outside shared buffers, and that the sort spilled to temp files because work_mem was small.
• The index option adds a composite index on account and created date with INCLUDE columns, so rows can be read already in order.
• The rewrite replaces OFFSET pagination with keyset pagination.
• The rollout uses CREATE INDEX CONCURRENTLY, checks that the new index is valid, and compares plans for a large and a small tenant.

Tips for better results

• Capture plans for both a typical and a worst case parameter. Fixes should help both.
• Re-run EXPLAIN (ANALYZE, BUFFERS) after each change and paste both versions back for comparison.
• Run ANALYZE before deeper changes. Sometimes fresh statistics alone fix the plan.

Mistakes to avoid

• Do not raise work_mem globally as the first fix. It applies per sort and per connection and can use far more memory than expected.
• Do not create indexes without CONCURRENTLY on busy tables. A normal build blocks writes.
• Do not drop an old index until you know no other query depends on it.

Who it is for

Backend developers, application teams without a dedicated DBA, and platform engineers who need to explain a slow query and its fix in a pull request.

A practical tuning loop

Reproduce the slow case with real parameters and capture the full plan. Run the prompt and pick the smallest change likely to fix the bottleneck, usually statistics or one index. Test it on staging, compare plans, then roll it out with a concurrent build. Keep the before and after plans in the pull request so the next person can see why the index exists.

Related PromptDig links

Open the PostgreSQL EXPLAIN ANALYZE Slow Query Reviewer: Plan Node Walkthrough, Row Estimate Misses, Buffer Reads, Index and Rewrite Options, and a Safe Rollout prompt with your query and plan. For more coding prompts, Browse more prompts. If you have a query tuning checklist, Share a prompt.