Every PostgreSQL developer eventually faces a query that runs for 40 seconds on a table with 800 million rows. You open EXPLAIN ANALYZE, see a wall of nested loops and seq scans, and the afternoon disappears. AI assistants can compress that investigation into minutes — if you ask precisely. Below are 12 prompts that I use in production work on PostgreSQL 15–17, each with a real SQL example, expected output, and the task it solves. Copy them, adapt the table names, and keep the reasoning visible: the value is not the answer, it is the process.
1. Decode an EXPLAIN (ANALYZE, BUFFERS) plan into plain English
Task: You have a slow query and a raw plan. Instead of guessing which node is expensive, ask the model to translate the plan and rank nodes by actual time.
Prompt:
You are a PostgreSQL performance engineer. Below is EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) output for a query on PostgreSQL 16. Do three things:
1. Summarize the plan as a tree: node type, actual time, rows, loops.
2. Identify the top 3 bottlenecks with reasoning (seq scan, nested loop explosion, sort spill, etc.).
3. Suggest concrete fixes: index type, statistics target, query rewrite.
Do not invent columns. If information is missing, say what to collect next.
Plan:
<PASTE PLAN>
Query:
<PASTE SQL>
Example plan fragment:
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
AND o.created_at > now() - interval '7 days';
-- Nested Loop (cost=0.86..184223.11 rows=1 width=40) (actual time=0.05..41230.77 rows=98211 loops=1)
-- -> Seq Scan on orders o (cost=0.00..172331.00 rows=1 width=16) (actual time=0.01..38900.12 rows=98211 loops=1)
-- Filter: ((status = 'pending') AND (created_at > (now() - '7 days'::interval)))
-- Rows Removed by Filter: 49901789
-- -> Index Scan using customers_pkey on customers c (cost=0.43..8.30 rows=1 width=32) (actual time=0.02..0.02 rows=1 loops=98211)
Expected result: The model flags the seq scan on orders (49.9M rows removed by filter) as the bottleneck and proposes a partial index:
CREATE INDEX CONCURRENTLY idx_orders_pending_recent
ON orders (created_at)
WHERE status = 'pending';
2. Find missing indexes from pg_stat_statements
Task: You inherited a database and don't know which queries hurt. Feed the top 20 statements to the model and ask which indexes would help most.
Prompt:
Here are the top 20 statements from pg_stat_statements sorted by total_exec_time. For each, tell me:
- whether an index would help and which columns to include,
- whether the query is a candidate for a partial or covering index,
- what the estimated write overhead is.
Output a Markdown table: queryid
| suggested_index | reason | expected_gain.
Query to collect data:
SELECT queryid, calls, total_exec_time, mean_exec_time, rows,
left(query, 120) AS sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
Expected result: For a statement like SELECT ... WHERE tenant_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC, the model proposes:
CREATE INDEX idx_docs_tenant_alive_created
ON documents (tenant_id, created_at DESC)
WHERE deleted_at IS NULL;
3. Refactor a slow JOIN into a semi-join or EXISTS
Task: Many slow queries are JOINs where you only need existence. The optimizer often does better with EXISTS.
Prompt:
Rewrite the JOIN below as EXISTS / NOT EXISTS or a lateral join where appropriate. Explain which form the planner handles better on PostgreSQL 16 and why. Keep semantics identical, including NULL handling.
Before:
SELECT DISTINCT u.id
FROM users u
JOIN logins l ON l.user_id = u.id
WHERE l.created_at > now() - interval '30 days';
Expected result:
SELECT u.id
FROM users u
WHERE EXISTS (
SELECT 1 FROM logins l
WHERE l.user_id = u.id
AND l.created_at > now() - interval '30 days'
);
The model explains that DISTINCT forces a sort/hash step, while EXISTS stops at the first match per user.
4. Design a partitioning strategy for a billion-row table
Task: A events table has 1.2B rows and grows 80M per month. You need range partitioning by month with minimal downtime.
Prompt:
Design a declarative range partitioning plan for PostgreSQL 16 for a table `events(id bigint, tenant_id int, created_at timestamptz, payload jsonb)` with 1.2B rows, 80M/month growth, mostly queried by tenant_id and last 90 days.
Include:
- partition key and interval,
- DDL to create the partitioned parent and first partitions,
- migration steps to move data from the existing table without long locks,
- indexes per partition vs. on parent,
- how to automate future partition creation (pg_partman or cron + DO block).
Expected result (excerpt):
CREATE TABLE events_p (
id bigint, tenant_id int,
created_at timestamptz NOT NULL, payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_09 PARTITION OF events_p
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE INDEX ON events_2026_09 (tenant_id, created_at DESC);
Plus a DO $$ block or pg_partman config to pre-create the next 3 months.
5. Tune VACUUM and autovacuum per table
Task: A high-churn table bloats because autovacuum never catches up. Global settings are dangerous; per-table tuning is safer.
Prompt:
A table `sessions` receives ~5k updates/sec, has 40M rows, and shows high n_dead_tup in pg_stat_user_tables. Propose per-table autovacuum settings and explain each parameter (autovacuum_vacuum_scale_factor, threshold, cost_limit). Include the ALTER TABLE statement and how to verify improvement via pg_stat_progress_vacuum.
Expected result:
ALTER TABLE sessions SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 1000,
autovacuum_vacuum_cost_limit = 2000
);
Verification query:
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'sessions';
6. Zero-downtime migration with CREATE INDEX CONCURRENTLY
Task: Add an index on a 500GB table without locking writes.
Prompt:
Write a safe migration to add an index on orders(customer_id, created_at DESC) to a 500GB table on PostgreSQL 16. Requirements: no long ACCESS EXCLUSIVE lock, handle failure of CONCURRENTLY, and provide a rollback. Include lock_timeout and statement_timeout settings.
Expected result:
SET lock_timeout = '5s';
SET statement_timeout = '0';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_cust_created
ON orders (customer_id, created_at DESC);
-- On failure, drop invalid index:
-- DROP INDEX CONCURRENTLY idx_orders_cust_created;
The model notes that CONCURRENTLY cannot run inside a transaction block and requires two table scans.
7. Detect and fix statistics staleness
Task: Planner picks bad plans because statistics are stale or the target is too low for skewed columns.
Prompt:
A column `status` has 5 distinct values but is heavily skewed (99% = 'done'). The planner estimates 20% selectivity, causing nested loops. Explain how to fix with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS and CREATE STATISTICS for correlated columns (status, created_at). Provide DDL and ANALYZE commands.
Expected result:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
CREATE STATISTICS orders_status_created (dependencies, ndistinct)
ON status, created_at FROM orders;
ANALYZE orders;
8. Rewrite a correlated subquery as a window function
Task: Per-group "latest row" queries are classic performance traps.
Prompt:
Rewrite this correlated subquery using DISTINCT ON or a window function. Compare plans and recommend the faster form on PostgreSQL 16 for a table with 100M rows and an index on (user_id, created_at DESC).
Before:
SELECT u.id,
(SELECT created_at FROM logins l
WHERE l.user_id = u.id
ORDER BY created_at DESC LIMIT 1) AS last_login
FROM users u;
Expected result:
SELECT DISTINCT ON (user_id) user_id, created_at AS last_login
FROM logins
ORDER BY user_id, created_at DESC;
The model explains that DISTINCT ON uses the index directly, avoiding N+1 subquery execution.
9. Choose between BRIN and B-tree for time-series
Task: Append-only tables with time ordering often benefit from BRIN, which is tiny compared to B-tree.
Prompt:
For an append-only table `metrics(device_id int, ts timestamptz, value numeric)` with 2B rows, compare BRIN vs B-tree on ts. Include size estimates, when BRIN wins, and the DDL for both. Mention pages_per_range.
Expected result:
CREATE INDEX idx_metrics_ts_brin ON metrics USING brin (ts)
WITH (pages_per_range = 32);
The model notes BRIN indexes can be orders of magnitude smaller but rely on physical correlation.
10. Explain lock contention from pg_locks
Task: A query hangs. You need to find the blocker chain fast.
Prompt:
Given output of pg_locks joined with pg_stat_activity, identify blocking chains, the root blocker, and the exact SQL. Suggest whether to cancel or terminate, and how to prevent recurrence with lock_timeout.
Query:
SELECT a.pid, a.query, l.locktype, l.mode, l.granted,
pg_blocking_pids(a.pid) AS blockers
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE NOT l.granted;
Expected result: The model points to the root PID holding ACCESS EXCLUSIVE and recommends SELECT pg_cancel_backend(pid); plus setting lock_timeout in the app.
11. Safe JSONB indexing and query patterns
Task: JSONB is flexible but easy to misuse. Ask for the right GIN operator class.
Prompt:
We store payload jsonb in events and query `payload @> '{"type":"click"}'` and `payload->>'country' = 'DE'`. Recommend GIN index options (jsonb_ops vs jsonb_path_ops), explain trade-offs, and give DDL. Also show how to index a single key with a btree expression index.
Expected result:
CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);
CREATE INDEX idx_events_country ON events ((payload->>'country'));
12. Generate a monitoring query pack
Task: You want a reusable set of queries for daily health checks.
Prompt:
Produce a set of 10 monitoring queries for PostgreSQL 16 covering: long-running queries, bloat, unused indexes, cache hit ratio, replication lag, checkpoint pressure, and table/index sizes. Each query must be read-only, safe on production, and include a short comment.
Expected result (sample):
-- Cache hit ratio (should be > 0.99 for OLTP)
SELECT sum(blks_hit) * 100.0 / nullif(sum(blks_hit + blks_read), 0) AS hit_ratio
FROM pg_stat_database;
-- Unused indexes
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
These 12 prompts cover the full lifecycle: reading plans, hunting indexes, rewriting queries, partitioning, vacuum tuning, and safe migrations. The common thread is specificity: always paste the real plan, the schema, and the PostgreSQL version. AI is a fast pair for reasoning, not an oracle — verify every recommendation with EXPLAIN ANALYZE on a staging copy. For deeper reading, the official docs at postgresql.org/docs/current/performance-tips.html and the pg_stat_statements reference remain the ground truth. Start with prompt #1 on your slowest query today, and iterate from there.
Sources: PostgreSQL 16 official documentation (EXPLAIN, partitioning, autovacuum), pg_stat_statements extension docs.
Comments