PostgreSQL Under the Hood: 14 Prompts That Turn 10-Second Queries Into 0.3s

PostgreSQL Under the Hood: 14 Prompts That Turn 10-Second Queries Into 0.3s

A query that takes 10 seconds in staging becomes 40 seconds in production once the table doubles. You run EXPLAIN ANALYZE, see a Seq Scan over two million rows, add an index — and the planner still ignores it because of a type mismatch. Sound familiar?

The pain isn't that PostgreSQL is slow. The pain is that the feedback loop is slow: read plan, hypothesize, rewrite, re-measure. An LLM assistant collapses that loop. It reads the plan output, spots the Rows Removed by Filter, proposes a rewrite and an index, and gives you the EXPLAIN command to verify the win.

Below are 14 copy-paste prompts for exactly that workflow — from reading EXPLAIN ANALYZE output to diagnosing lock contention with pg_stat_activity. Every example uses real PostgreSQL syntax (verified against the official docs). Swap in your own schema and queries.

1. Decode an EXPLAIN ANALYZE plan line by line

When to use: you have the plan but the numbers don't tell you where the time went.

You are a PostgreSQL performance engineer. Below is the output of
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) for a query on PostgreSQL 16.

For each node, explain in plain English:
1. What operation it performs (Seq Scan, Nested Loop, Hash Join, Sort...)
2. Whether actual rows diverge from estimated rows, and what that implies
   for statistics quality
3. The role of 'Rows Removed by Filter' and 'Buffers: shared read/hit'
4. Which node is the bottleneck and why

Then give me 3 concrete fixes, ordered by expected impact, each with the
SQL to apply and the EXPLAIN command to verify it.

<plan>
{PASTE PLAN HERE}
</plan>

Example: a plan showing Seq Scan on orders (cost=0.00..48210.00 rows=1 width=64) (actual time=0.012..8421.337 rows=1200000 loops=1) with Rows Removed by Filter: 3800000 means the planner expected 1 row but got 1.2M — statistics are stale and the filter column is unindexed. Fix: ANALYZE orders; then CREATE INDEX ON orders (customer_id);.

2. Generate the right index for a slow WHERE clause

When to use: a query filters on several columns and you don't know whether to use a single, composite, or partial index.

Given this query and table definition, recommend the optimal index type.
Consider: B-tree vs GIN vs BRIN, column order in a composite index,
partial indexes with a WHERE clause, and covering indexes with INCLUDE.

Return:
- The exact CREATE INDEX statement
- Why this column order
- What write overhead it adds (approx.)
- The EXPLAIN command that proves the planner uses it

<query>{YOUR QUERY}</query>
<schema>{CREATE TABLE ...}</schema>

Example: for SELECT * FROM events WHERE tenant_id = $1 AND created_at > now() - interval '7 days' ORDER BY created_at DESC, the correct answer is a composite B-tree CREATE INDEX ON events (tenant_id, created_at DESC); — leading column equality first, then range. A partial index WHERE tenant_id = 42 only helps if that tenant dominates traffic.

3. Rewrite a correlated subquery as a JOIN or lateral

When to use: the plan shows a SubPlan executed once per outer row.

Rewrite this query to eliminate the correlated subquery.
Show two alternatives: (a) a JOIN with GROUP BY, (b) a LATERAL join.
For each, state the trade-off (readability, memory, parallel-safety).
Then give an EXPLAIN (ANALYZE, BUFFERS) comparison template.

<query>{YOUR QUERY}</query>

Example: SELECT u.name, (SELECT count(*) FROM orders o WHERE o.user_id = u.id) FROM users u; becomes SELECT u.name, coalesce(o.cnt,0) FROM users u LEFT JOIN (SELECT user_id, count(*) cnt FROM orders GROUP BY user_id) o ON o.user_id = u.id;. The planner can hash-aggregate orders once instead of scanning it per user.

4. Diagnose why the planner ignores your index

When to use: the index exists, but the plan still shows Seq Scan.

The index {INDEX NAME} exists on {TABLE}({COLUMNS}), but EXPLAIN shows a
sequential scan. Walk through these causes in order and tell me which
applies:
1. Type mismatch (e.g. comparing uuid to text, timestamp to date)
2. Non-sargable predicate (function on the indexed column, LIKE '%x%')
3. Low selectivity — the planner estimates too many rows
4. Stale statistics / missing ANALYZE
5. Cost settings (random_page_cost, effective_cache_size) misconfigured
6. Collation or operator class mismatch
Give the exact query to test each hypothesis.

Example: WHERE created_at::date = '2026-09-10' forces a cast on the column, making the B-tree unusable. Rewrite as WHERE created_at >= '2026-09-10' AND created_at < '2026-09-11'.

5. Tune work_mem and detect disk spills

When to use: the plan says Sort Method: external merge Disk: 24576kB.

My query spills to disk during Sort or Hash. Given the plan below:
- Estimate the minimum work_mem that would keep it in memory
- Show whether to set it per-session or per-query
- Warn me about the memory risk if many sessions run concurrently
- Provide the SET LOCAL command scoped to a transaction

<plan>{PASTE PLAN}</plan>

Example: SET LOCAL work_mem = '128MB'; inside a transaction is safer than a global change, because work_mem is allocated per sort/hash node, not per connection — a global bump can exhaust RAM under load.

6. Find the top queries by total time with pg_stat_statements

When to use: you need to know which query to optimize first.

Write a query against pg_stat_statements that returns the 10 statements
with the highest total_exec_time, showing calls, mean_exec_time,
rows, and the query text truncated to 100 chars.
Exclude pg_stat_statements itself and system queries.

SELECT substring(query, 1, 100) AS short_query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

Requires CREATE EXTENSION pg_stat_statements; and shared_preload_libraries = 'pg_stat_statements' in postgresql.conf (see the docs).

7. Detect N+1 query patterns from an ORM log

When to use: your app fires hundreds of tiny queries instead of one join.

Here is a slice of my application log with SQL statements.
Identify N+1 patterns: repeated identical queries differing only by a
parameter. For each pattern, show the equivalent single-query rewrite
and name the ORM feature that fixes it (selectinload, includes,
prefetch_related, etc.).

<log>{PASTE LOG}</log>

Example: 200 log lines of SELECT * FROM products WHERE id = $1 with different ids collapse into SELECT * FROM products WHERE id = ANY($1);. In SQLAlchemy that's selectinload(); in Django, select_related()/prefetch_related().

8. Read a lock contention snapshot from pg_stat_activity

When to use: queries hang and you suspect a lock, not a slow plan.

Analyze this pg_stat_activity snapshot. Identify:
- Sessions in 'idle in transaction' state holding locks
- Blocking vs waiting PIDs (use pg_blocking_pids)
- The offending query text and how long it has run
Recommend: terminate vs wait, and how to prevent it (statement_timeout,
idle_in_transaction_session_timeout).

<snapshot>{PASTE OUTPUT}</snapshot>

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS runtime,
       pg_blocking_pids(pid) AS blockers,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC;

9. Choose between VACUUM, ANALYZE, and REINDEX

When to use: tables bloat, statistics drift, or indexes fragment.

Explain the difference between VACUUM, VACUUM FULL, ANALYZE, and REINDEX
in terms of locks taken and when each is appropriate.
Then, given symptoms {bloat / stale stats / index bloat}, tell me which to
run and why VACUUM FULL is often the wrong first choice in production.

Key fact: VACUUM FULL rewrites the table and takes an ACCESS EXCLUSIVE lock — blocking reads and writes. Plain VACUUM runs concurrently and usually suffices; reserve VACUUM FULL for maintenance windows.

10. Design a covering index to enable Index Only Scan

When to use: the query touches few columns and you want to skip the heap entirely.

Given this SELECT list and WHERE clause, propose a covering index using
INCLUDE so the planner can choose an Index Only Scan.
Explain how the visibility map must be up to date (VACUUM) for the
'Heap Fetches: 0' line to appear in EXPLAIN.

<query>{YOUR QUERY}</query>

Example: CREATE INDEX ON orders (customer_id) INCLUDE (total, status); lets SELECT total, status FROM orders WHERE customer_id = 5; run as Index Only Scan — provided VACUUM has marked the pages all-visible.

11. Convert OFFSET pagination to keyset (cursor) pagination

When to use: page 500 of a listing takes seconds because OFFSET 50000 scans and discards rows.

Rewrite this OFFSET/LIMIT query as keyset pagination.
Return the first-page query, the next-page query using the last seen
(cursor) values, and the index that supports it.
Note the edge cases: non-unique sort keys, NULLs, and descending order.

<query>{SELECT ... ORDER BY created_at DESC OFFSET 50000 LIMIT 20}</query>

Example: replace OFFSET 50000 LIMIT 20 with WHERE (created_at, id) < ($last_created_at, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20;, backed by CREATE INDEX ON posts (created_at DESC, id DESC);. Cost is O(page size), not O(offset).

12. Generate a reproducible benchmark script

When to use: you must prove a rewrite is actually faster, not just look faster.

Write a psql script that benchmarks two versions of a query fairly:
- Warm the cache first
- Run each query N times with \timing on
- Report min, median, and max latency
- Use the same session and settings
Avoid pgbench complexity; keep it to \timing and a DO block if needed.

Tip: always run each query at least 5–10 times and report the median, not a single run. The first execution pays for cache misses; later runs show steady-state latency.

13. Explain partitioning trade-offs for a large time-series table

When to use: a single table grows past tens of millions of rows.

Should I partition this table by RANGE on created_at? Consider:
- Query patterns (does every query filter on the partition key?)
- Partition pruning and its effect on EXPLAIN
- Index maintenance per partition vs one big index
- Migration path: CREATE TABLE ... PARTITION BY RANGE plus ATTACH PARTITION
Give the DDL for monthly partitions and a sample pruned plan.

Example: CREATE TABLE events (id bigint, created_at timestamptz) PARTITION BY RANGE (created_at); then CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');. A query with WHERE created_at >= '2026-09-01' scans only the September partition — visible as Subplans Removed in EXPLAIN.

14. Get a prioritized optimization checklist from one plan

When to use: you want the single highest-leverage fix, not a lecture.

Given this EXPLAIN (ANALYZE, BUFFERS) output, return a prioritized
to-do list. For each item: the expected impact (high/medium/low), the
SQL to run, and the risk. Sort by impact. Do not suggest anything that
requires downtime unless nothing else works.

<plan>{PASTE PLAN}</plan>

Example output shape: (1) high — ANALYZE orders; to fix a 1-vs-1.2M row estimate; (2) high — add composite index; (3) medium — raise work_mem for this query only; (4) low — revisit random_page_cost for SSD storage.

Quick reference: which prompt for which symptom

Ground rules that keep these prompts honest

Always measure, never assume. A rewrite that looks faster can be slower once the planner re-estimates. Every prompt above ends with an EXPLAIN (ANALYZE, BUFFERS) verification step for a reason — the planner's cost model, not intuition, decides the winner.

Keep statistics fresh. Most "the index isn't used" cases trace back to stale pg_statistic. Autovacuum handles this in normal operation, but bulk loads need a manual ANALYZE.

Read the primary sources. The PostgreSQL documentation on Using EXPLAIN, the Performance Tips chapter, and the pg_stat_statements reference are the ground truth. Treat every LLM suggestion as a hypothesis and confirm it against a real plan on your data.

Start with prompt #6 to find your worst offender, then #1 to understand it, then #14 to fix it in priority order. Copy the prompts, swap in your SQL, and let the plan — not the model — confirm the 10x.