PostgreSQL Under Pressure: 12 Battle-Tested Prompts for Query Optimization, Indexes, and Partitioning in 2026

PostgreSQL Under Pressure: 12 Battle-Tested Prompts for Query Optimization, Indexes, and Partitioning in 2026

Every PostgreSQL engineer eventually meets the same wall: the query that ran in 40 ms on staging now takes 9 seconds in production, and pg_stat_activity is a wall of red. In 2026 the difference between a DBA who firefights at 3 a.m. and one who ships a fix before lunch is rarely raw knowledge — it's leverage. LLMs have become a real second pair of eyes for reading EXPLAIN ANALYZE output, drafting index strategies, and reasoning about partition schemes. But only if you prompt them with the right context.

This isn't a list of "ask ChatGPT to optimize my query." Every prompt below is a structured template you paste into an AI agent (Cursor, Claude, GPT-5-class models) together with real artifacts: the plan, the schema, pg_stat_user_tables, autovacuum settings. The model does the tedious pattern-matching; you make the call. PostgreSQL 17 is the current stable line as of late 2026, with 18 in release candidate — syntax below stays within 14+ compatibility unless noted.

A quick primer for newcomers: EXPLAIN ANALYZE runs the query and shows the actual plan with timing and row counts; EXPLAIN (ANALYZE, BUFFERS) adds I/O stats, which is where the truth usually hides. pg_stat_statements aggregates query fingerprints across the whole instance. These two extensions are the backbone of everything that follows — enable them first (CREATE EXTENSION pg_stat_statements;).

1. The Plan Interpreter

For: Decoding a raw EXPLAIN (ANALYZE, BUFFERS) output when the numbers look wrong but you can't spot why.

Prompt:

You are a PostgreSQL performance engineer. Below is EXPLAIN (ANALYZE, BUFFERS)
output for a slow query. Table schema and indexes are attached.

1. Identify the node where actual rows differ most from estimated rows (the
   cardinality misestimate) and explain WHY the planner got it wrong.
2. List every sequential scan and whether an index would help or hurt.
3. Flag any nested loop with high loop count over a large inner relation.
4. Suggest the single highest-impact change first, then secondary ones.
5. Output a table:

| Node | Est rows | Actual rows | Ratio | Suspect |

Do not suggest adding indexes until you've explained the misestimate.

PLAN:
<paste>
SCHEMA:
<paste \d+ output>

Example use: A JOIN between orders and customers where the planner estimated 1 row but got 40,000 — usually a stale statistics problem. The prompt forces the model to name the cause (e.g., correlated columns not covered by ANALYZE) before jumping to "add an index," which is the most common bad advice.

Why it works: Cardinality misestimates cause the majority of bad plans; naming the ratio makes the fix obvious.

2. Index Advisor with Write-Cost Awareness

For: Deciding whether a proposed index is worth the write amplification.

Prompt:

Given this query and table, propose candidate indexes. For each candidate:
- Exact CREATE INDEX statement (specify CONCURRENTLY where relevant)
- Which predicate/join it serves
- Estimated benefit (which plan node it eliminates)
- Write cost: how many INSERT/UPDATE/s on this table per day
  (from pg_stat_user_tables.n_tup_ins + n_tup_upd)
- Whether a partial index (WHERE ...) or covering index (INCLUDE ...) is better

Reject any index that duplicates a prefix of an existing one. List existing
indexes from pg_indexes first.

QUERY:
<paste>
TABLE STATS:
<paste pg_stat_user_tables row>

Example use: For a WHERE status = 'pending' AND created_at > now() - interval '1 day' query, the model typically recommends a partial index ON orders (created_at) WHERE status = 'pending' — far smaller and cheaper to maintain than a full composite index.

Why it works: The write-cost column stops you from carpet-bombing indexes on a hot OLTP table.

3. The Index Bloat Detective

For: Tables where indexes have grown to absurd sizes after heavy UPDATE/DELETE churn.

Prompt:

Here is pgstattuple output and pg_stat_user_indexes for a table with heavy
churn. Determine:
1. Which indexes have >30% bloat and are candidates for REINDEX CONCURRENTLY.
2. Whether autovacuum is keeping up: compare n_dead_tup vs n_live_tup
   and last_autovacuum.
3. Whether fillfactor should be lowered (e.g. to 70) for HOT updates.
4. A concrete maintenance plan: schedule, commands, and monitoring query.

DATA:
<paste>

Example use: An updated_at-heavy table where the primary key index bloats because updates move rows. Lowering fillfactor to 70 and running REINDEX INDEX CONCURRENTLY typically reclaims significant space with no downtime (CONCURRENTLY avoids the exclusive lock).

4. Partitioning Strategy Designer

For: Choosing between range, list, and hash partitioning for a growing table.

Prompt:

I have a table growing by ~N rows/day, currently M GB. Access patterns:
- 90% of queries filter on <column>
- Retention: delete data older than <period>
- Writes are append-only / have hot updates

Recommend a partitioning strategy. Compare:
1. RANGE by <candidate column> — pros, cons, partition count
2. HASH by <candidate column> — when it beats range
3. Declarative partitioning (PG 10+) vs native inheritance
Include the exact DDL for the parent and first partitions, plus how to automate
future partition creation (pg_partman or a cron + DO block).
Warn me about the cost of queries that DON'T include the partition key.

Example use: A time-series events table with 90-day retention is a textbook RANGE case. The prompt surfaces the real trap: a query without the partition key scans every partition, so partitioning is useless unless your access pattern matches.

5. The Partition Pruning Verifier

For: Confirming that the planner actually prunes partitions at runtime.

Prompt:

Here is EXPLAIN output for a query against a partitioned table. Confirm:
1. Are partitions being pruned at PLAN time? Look for 'Subplans Removed'.
2. If the filter uses a non-constant (e.g. a subquery or parameter),
   is runtime pruning happening? (PG 11+ feature)
3. If pruning is NOT happening, list the exact reasons:
   - function on the partition key (e.g. date_trunc)
   - type mismatch between query and partition key
   - OR conditions spanning partitions
4. Rewrite the query so pruning works.

PLAN:
<paste>

Example use: WHERE date_trunc('day', created_at) = '2026-10-01' defeats pruning because the function wraps the partition key. Rewriting to WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02' restores it. This is one of the most common silent performance killers.

6. The N+1 Killer

For: ORMs generating hundreds of tiny queries instead of one join.

Prompt:

From pg_stat_statements, here are the top queries by total_exec_time.
Identify N+1 patterns: the same query fingerprint executed thousands of times
with tiny row counts. For each:
1. Show the ORM-agnostic SQL that replaces the loop with a single JOIN or
   a batched WHERE id = ANY($1).
2. Explain the trade-off vs a lateral join.
3. Note if a covering index would make the batched version index-only.

pg_stat_statements:
<paste top rows>

Example use: Spotting SELECT * FROM addresses WHERE user_id = $1 called 50,000 times. Batching into WHERE user_id = ANY($1::int[]) cuts round-trips dramatically — the exact gain depends on your network latency, but the pattern is universally worth fixing.

7. Autovacuum Tuning Assistant

For: Tables where dead tuples accumulate faster than autovacuum cleans them.

Prompt:

Given these per-table autovacuum settings and pg_stat_user_tables metrics,
recommend tuning. For each table:
- Is it a candidate for per-table autovacuum_vacuum_scale_factor override?
- Should autovacuum_vacuum_cost_limit be raised for this table?
- Is a manual VACUUM (ANALYZE) needed now?
Output ALTER TABLE ... SET (autovacuum_...) statements with justification.

DATA:
<paste>

Example use: A large append-heavy table where the default scale factor (0.2) means autovacuum waits until 20% of the table is dead. Setting autovacuum_vacuum_scale_factor = 0.01 for that table triggers cleanup far earlier and keeps bloat down.

8. Lock Contention Debugger

For: Queries that stall, not because they're slow, but because they're blocked.

Prompt:

Here is pg_locks joined with pg_stat_activity showing blocked and blocking PIDs.
1. Build the wait chain (who blocks whom, root blocker).
2. Identify the lock mode (RowExclusiveLock, AccessExclusiveLock, etc.) and
   whether it comes from DDL, an uncommitted transaction, or a long SELECT.
3. For each root blocker, suggest: kill it, wait, or restructure.
4. Recommend a lock_timeout / statement_timeout value for the offender.

DATA:
<paste pg_locks + pg_stat_activity>

Example use: An ALTER TABLE ... ADD COLUMN (AccessExclusiveLock) queued behind a long-running SELECT, blocking everything behind it. The fix: set lock_timeout so the DDL fails fast instead of creating a pileup.

9. The CTE vs Subquery Rewriter

For: Queries where a CTE is accidentally acting as an optimization fence.

Prompt:

Rewrite this query to remove optimization fences. Note:
- In PG 12+, non-recursive CTEs are inlined by default UNLESS they are
  referenced multiple times or use VOLATILE functions or are recursive.
- Identify if a CTE here is preventing predicate pushdown.
- Compare the original and rewritten plans (paste both EXPLAIN outputs).
Do not change semantics — only the execution shape.

QUERY:
<paste>

Example use: A CTE referenced twice that forces materialization. Converting one reference to a subquery, or adding MATERIALIZED/NOT MATERIALIZED explicitly, gives the planner freedom. The prompt insists on semantic equivalence — critical when refactoring.

10. Connection Pool & pgBouncer Sizer

For: "Too many connections" errors under load spikes.

Prompt:

We run PostgreSQL with max_connections = N and use pgBouncer.
Current: X app instances, Y threads each, Z avg query duration.
1. Compute required pool size using Little's Law (concurrency = throughput x latency).
2. Recommend pgBouncer pool_mode (session/transaction/statement) and why.
3. Flag any feature that breaks in transaction mode (prepared statements,
   SET, LISTEN/NOTIFY, advisory locks).
4. Give the exact pgbouncer.ini keys to change.

NUMBERS:
<paste>

Example use: Transaction pooling is usually the right default for stateless web apps, but it breaks session-level features. The prompt forces you to check your app for SET statements and prepared statements before switching — a mistake that causes subtle production bugs.

11. Slow-Query Regression Detector

For: Catching a query that got slower after a deploy, before users complain.

Prompt:

Compare pg_stat_statements snapshots from before and after a deploy.
For each query whose mean_exec_time increased >20%:
1. Show old vs new mean, calls, and total time.
2. Hypothesize the cause: plan change, data growth, missing stats, new index
   that the planner now prefers wrongly.
3. Suggest whether to run ANALYZE, add an index, or force a plan via
   a statistics target increase.

BEFORE:
<paste>
AFTER:
<paste>

Example use: A query flips from index scan to sequential scan after a table crosses a size threshold. Raising default_statistics_target on the relevant column, or running ANALYZE, often restores the good plan without touching the query.

12. The EXPLAIN Diff Explainer

For: Understanding why two plans for the "same" query differ across environments.

Prompt:

Here are two EXPLAIN plans for the same SQL on two databases (prod vs replica).
1. List every structural difference in node order and join method.
2. Explain each difference via: statistics, work_mem, random_page_cost,
   effective_cache_size, or data distribution.
3. Tell me which GUC to check with SHOW on each environment.

PLAN A:
<paste>
PLAN B:
<paste>

Example use: The same query uses a hash join in prod and a nested loop on a replica with different work_mem. The prompt names the GUC to align — a frequent source of "works on my machine" confusion across environments.

Real Case: The 9-Second Dashboard

A SaaS team had a reporting dashboard timing out. pg_stat_statements showed one query at 9.2 s, called on every page load. Using prompts 1 and 2, they found the planner estimating 1 row against 40,000 actual (stale stats on a filtered column). ANALYZE alone dropped it to 300 ms; a partial index brought it to 40 ms. No schema change, no downtime. The whole diagnosis took under an hour — with the model doing the plan-reading grunt work and the engineer validating each claim against the actual database.

The recurring lesson across all twelve prompts: never trust the model's output blindly. Run every suggested EXPLAIN, measure before and after, and roll back anything that doesn't move the numbers. The AI accelerates the reasoning; the database is the source of truth.

How to Use These Prompts Without Breaking Production

For authoritative references, consult the official PostgreSQL documentation on EXPLAIN, partitioning, and pg_stat_statements. The PostgreSQL wiki's Performance Optimization page and the pg_partman extension docs are worth bookmarking.

PostgreSQL in 2026 rewards engineers who combine deep fundamentals with fast tooling. These twelve prompts won't replace knowing what a nested loop costs — but they compress the boring half of the job (reading plans, cross-checking stats, drafting DDL) into minutes, leaving you the interesting half: deciding what to change and proving it worked. Start with prompt 1 on your slowest query today, measure the before, and let the numbers — not the model — tell you when you're done. Your 3 a.m. self will thank you.