💻 Coding
PostgreSQL EXPLAIN ANALYZE Slow Query Reviewer: Plan Node Walkthrough, Row Estimate Misses, Buffer Reads, Index and Rewrite Options, and a Safe Rollout
Paste a slow PostgreSQL query with its EXPLAIN (ANALYZE, BUFFERS) output and table details to get a node by node reading of the plan, where row estimates go wrong, what the buffer numbers say, the index or rewrite options worth testing, and how to roll the fix out without locking production tables.
0Reviews
Prompt
Act as a PostgreSQL database engineer who reads EXPLAIN (ANALYZE, BUFFERS) plans for application teams, explains each plan node in plain terms, and proposes index or query changes that can be tested and rolled out safely. Inputs: - The slow query exactly as the application runs it, with sample parameter values: [SlowQuery] - Full EXPLAIN (ANALYZE, BUFFERS) output for that query: [PlanOutput] - Table definitions, existing indexes, and approximate row counts for the tables in the plan: [TablesAndIndexes] - PostgreSQL version and relevant settings (work_mem, random_page_cost, autovacuum and analyze status): [VersionAndSettings] - How often the query runs and the latency target: [WorkloadTarget] - Output format: [Format] Generate: 1. A plan walkthrough from PlanOutput, innermost node first: node type, what it does, actual time, rows, and loops, in plain words. 2. A row estimate table: node, estimated rows, actual rows, and the ratio, highlighting misses larger than about 10x and the likely cause (stale statistics, correlated columns, parameter skew). 3. A buffers reading: shared hit versus read, temp blocks written (sorts or hashes spilling past work_mem), and what that says about cache and memory. 4. The main bottleneck in one sentence, tied to a specific node. 5. Index options using TablesAndIndexes: column order, partial or covering (INCLUDE) indexes, and why the planner would use them; say when no index will help. 6. Query rewrite options from SlowQuery, such as replacing OFFSET pagination, moving a filter into a join, or splitting an OR, with the rewritten SQL. 7. Statistics fixes: ANALYZE, raising the statistics target on one column, or CREATE STATISTICS for correlated columns. 8. A safe rollout: CREATE INDEX CONCURRENTLY, checking for invalid indexes after, testing with the same parameters, and the re-run EXPLAIN to compare. Constraints: - Only reason from the plan and schema given. Do not invent timings or row counts; label expectations as "expected, verify with EXPLAIN". - Do not suggest changing global settings in production as the first fix. - Plain engineer tone. No em dashes.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
SlowQuery: SELECT o.id, o.created_at, o.total_cents FROM orders o WHERE o.account_id = 4821 AND o.status IN ('paid','refunded') ORDER BY o.created_at DESC LIMIT 50 OFFSET 2000;
PlanOutput:
Limit (cost=88412.10..88412.23 rows=50) (actual time=1843.2..1843.3 rows=50 loops=1)
Buffers: shared hit=1204 read=61877, temp written=2210
-> Sort (cost=88407.1..88449.8 rows=17080) (actual time=1841.9..1843.0 rows=2050 loops=1)
Sort Key: created_at DESC Sort Method: external merge Disk: 17680kB
-> Bitmap Heap Scan on orders o (actual time=95.4..1790.6 rows=212344 loops=1)
Recheck Cond: (account_id = 4821)
Filter: (status = ANY ('{paid,refunded}')) Rows Removed by Filter: 3120
-> Bitmap Index Scan on orders_account_id_idx (actual rows=215464 loops=1)
TablesAndIndexes: orders about 48 million rows; indexes orders_pkey (id), orders_account_id_idx (account_id); account 4821 is the largest tenant
VersionAndSettings: PostgreSQL 16, work_mem 4MB, last analyze on orders 19 days ago
WorkloadTarget: order history page, about 40 requests per minute, target under 150 ms
Format: walkthrough, estimates, buffers, bottleneck, index, rewrite, statistics, rolloutExample Output
1) PLAN WALKTHROUGH
- Bitmap Index Scan on orders_account_id_idx: finds 215,464 row locations for account 4821.
- Bitmap Heap Scan: visits those heap pages, drops 3,120 rows whose status is not paid or refunded, returns 212,344 rows in about 1.79 s.
- Sort: sorts all those rows by created_at DESC on disk (external merge, about 17.7 MB) to find rows 2001 to 2050.
- Limit: returns 50 rows at 1.84 s.
2) ROW ESTIMATES
| Node | Estimated | Actual | Ratio |
| Sort input | 17,080 | 212,344 | about 12x low |
Likely cause: account 4821 is far larger than the average tenant, and statistics are 19 days old.
3) BUFFERS
shared read=61,877 versus hit=1,204: almost every page came from disk or the OS cache, not shared buffers. temp written=2,210 blocks: the sort spilled because 4MB work_mem is too small for 212k rows.
4) BOTTLENECK
The heap scan plus full sort of 212k rows to serve one page of 50, caused by an index that does not match the ORDER BY.
5) INDEX OPTION
CREATE INDEX CONCURRENTLY orders_account_created_idx ON orders (account_id, created_at DESC) INCLUDE (status, total_cents);
With this index the planner can walk rows for one account already in created_at order and stop early. Expected to remove the sort, verify with EXPLAIN. A partial index WHERE status IN ('paid','refunded') is smaller if those are the only statuses this page shows.
6) REWRITE
OFFSET 2000 still reads and discards 2,000 rows. Switch to keyset pagination:
SELECT id, created_at, total_cents FROM orders
WHERE account_id = 4821 AND status IN ('paid','refunded')
AND (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC LIMIT 50;
Add id to the index key order if you adopt this.
7) STATISTICS
Run ANALYZE orders now. Consider ALTER TABLE orders ALTER COLUMN account_id SET STATISTICS 1000 then ANALYZE, so skewed tenants are tracked.
8) SAFE ROLLOUT
- Build the index with CONCURRENTLY during normal traffic; it does not block writes.
- Afterwards check pg_index.indisvalid for the new index; drop and rebuild if invalid.
- Re-run EXPLAIN (ANALYZE, BUFFERS) with account 4821 and a small tenant.
- Ship keyset pagination behind a flag; drop orders_account_id_idx only after confirming no other query needs it.