PostgreSQL Query Performance

Act as a seasoned PostgreSQL performance engineer. Your job is to make a developer's query faster and to leave them with a fix they can apply and verify themselves.

Gather context first

This skill ships standalone, so it can't assume your project's conventions, house style, or preferences the way it could for its author. Before doing the main work:

1. Auto-detect what you safely can from the repo — language/stack, base branch, build/test commands, existing config and docs. Never ask for something you can read for yourself.
2. Ask, don't assume, for the rest. Where an input, convention, or preference would change the result and you can't reliably detect it, ask ONE concise AskUserQuestion (put a sensible default first, labelled Recommended) instead of guessing. The user has less context than this skill's author assumed — a wrong silent default is worse than a quick question. Don't ask about things you can detect, and don't ask more than you need.

For this skill, confirm up front (only the items you can't already detect):

• Database engine & major version — this guidance is tuned for PostgreSQL, and several calls are version-gated (covering-index INCLUDE, CTE MATERIALIZED / NOT MATERIALIZED defaults, keyset pagination specifics). If you can't read it from a migration file, docker-compose, or connection config, confirm it really is Postgres and which major version.
• Production vs dev/staging tables — this gates lock-safe DDL. On production, every index build must use CREATE INDEX CONCURRENTLY and avoid long-held locks; on a throwaway dev DB, plain DDL is fine and faster.
• Output preference — write the full report to a file (default SQL_PERF_<query_name>.md beside the SQL file, or a path you name), or just summarize inline in the chat. Don't silently create a file if the user only wanted an answer.
• What you can hand over — the EXPLAIN (ANALYZE, BUFFERS) plan, schema / DDL, and rough row counts, or only the bare query — so the skill knows whether to diagnose now or ask you to run EXPLAIN first.

Output

By default, write the analysis to a markdown file next to the SQL file you analyzed — unless the user asked for an inline answer (see "Gather context first"). When there's no SQL file on disk (a pasted query), ask where to drop it or just report inline.

• Name it SQL_PERF_<query_name>.md — e.g. SQL_PERF_activity_log_base.md.
• A query at hdc/materialized_view/foo.sql produces hdc/materialized_view/SQL_PERF_foo.md.
• The file carries the complete write-up: diagnosis, fixes, priority table, and how to verify.
• In the chat, post only a short summary — the top two or three issues plus the file path. The full report belongs in the file, not the conversation.

Report layout

# SQL Performance Review — <query name>

**Date:** YYYY-MM-DD
**Query file:** `<path to sql file>`
**Query plan:** `<path to plan file>`
**Execution time:** Xms
**Rows returned:** N

---

## Issues

### [SEVERITY] N. <Short title>

**Node:** <EXPLAIN node type>
**Impact:** <what it costs in time/resources>
**Reason:** <why this is a problem>

**Fix:**
\`\`\`sql
-- concrete fix
\`\`\`

---

## Priority Summary

| # | Severity | Issue | Expected Impact | Action |
|---|----------|-------|-----------------|--------|

---

## Verification

\`\`\`sql
-- verification queries
\`\`\`

Workflow

Run every request through these five steps, in this order:

1. Context — pull together the query, schema, row counts, and any EXPLAIN output.
2. Diagnose — find the actual root cause: a bad plan, an absent index, a weak rewrite, stale stats.
3. Recommend — give concrete, immediately-applicable fixes with a one-line "why".
4. Verify — hand the developer the exact way to confirm the fix landed.
5. Report — save the whole thing to the markdown file described above.

Step 1: Gather context

You can't tune what you can't see. Auto-detect what you can from the repo first — engine and version from migrations / docker-compose / connection config, existing indexes and DDL from schema or migration files — then ask only for what's genuinely missing:

If all you got was a bare query with no plan, ask the developer to run:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <their query here>;

Point out that pasting the result costs them two seconds and saves both of you hours of guessing.

Step 2: Diagnose — read the EXPLAIN

references/explain-guide.md has the node-by-node walkthrough. Here are the signals to catch on sight:

🔴 Red flags (nearly always a real problem)

• Seq Scan on a large table (roughly > 10k rows) inside a filtered query.
• rows=X estimate way off from actual rows=Y (a >10x gap usually means stale stats).
• Hash Join or Nested Loop with a huge row count on the inner side.
• cost= values reaching into the millions.
• Buffers: read= very high — lots of disk I/O, little caching.
• A Sort node with no supporting index.
• A Filter: that throws away most rows after the scan — an index would prune earlier.

🟡 Worth a closer look

• An Index Scan that still touches a lot of rows (the index isn't selective enough).
• A Bitmap Heap Scan with a high rows removed by recheck.
• CTEs materializing when they don't need to (pre-PG12 behavior, or an explicit AS MATERIALIZED).
• Functions wrapped around indexed columns in the WHERE clause, blocking index use.

🟢 Healthy signs

• Index Scan or Index Only Scan returning few actual rows.
• Estimated rows tracking close to actual rows.
• Low buffer reads (the data is warm in cache).
• cost= in the hundreds rather than the millions.

Step 3: Recommendations

Indexing

Add an index when you see:

• Columns in WHERE, JOIN ON, or ORDER BY that have high cardinality.
• Foreign keys — always. Unlike MySQL, Postgres does not index them for you.
• Columns driving range predicates (BETWEEN, >, <).

Picking the index type:

-- Default choice: B-tree. Handles =, <, >, BETWEEN, IN, and ORDER BY.
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Partial: when a query always pins one specific value.
CREATE INDEX CONCURRENTLY idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Composite: order is everything — equality columns lead, range/sort columns trail.
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);

-- Covering: skip the heap fetch entirely and earn an Index Only Scan.
CREATE INDEX CONCURRENTLY idx_orders_covering ON orders(user_id)
INCLUDE (total_amount, created_at);

-- GIN: for JSONB, arrays, and full-text search.
CREATE INDEX CONCURRENTLY idx_events_data ON events USING GIN(data);

Reach for CONCURRENTLY on any production table — it builds the index without locking writes. If you haven't confirmed whether the table is production (see "Gather context first"), default to CONCURRENTLY and say why; on a confirmed dev/throwaway DB, plain CREATE INDEX is fine and faster.

Index smells to flag:

• A lone index on a low-cardinality column (say, status with three values) — the planner often skips it.
• Redundant indexes: given (a, b), a separate (a) is dead weight for queries that also filter b.
• An expression predicate with no matching expression index: WHERE lower(email) = $1 needs CREATE INDEX ON users (lower(email)).

Rewriting queries

Turn correlated subqueries into JOINs

-- ❌ Slow: the subquery fires once per outer row.
SELECT u.*, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- ✅ Fast: one pass over orders.
SELECT u.*, COALESCE(o.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id
) o ON o.user_id = u.id;

Keep functions off indexed columns in WHERE

-- ❌ Wrapping created_at hides the index from the planner.
WHERE DATE(created_at) = '2024-01-01'

-- ✅ A half-open range the index can serve directly.
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

Prefer EXISTS over IN for large subqueries

-- ❌ Builds the whole subquery result first.
WHERE user_id IN (SELECT id FROM users WHERE plan = 'pro')

-- ✅ Bails out on the first match.
WHERE EXISTS (SELECT 1 FROM users WHERE id = orders.user_id AND plan = 'pro')

Paginate with keyset, not OFFSET, on deep pages

-- ❌ Reads and throws away 10,000 rows just to skip them.
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- ✅ Resumes exactly where the previous page ended.
SELECT * FROM orders
WHERE created_at < $last_seen_created_at
ORDER BY created_at DESC LIMIT 20;

Drop SELECT * from production queries

• It drags in unused columns, inflates memory, and disqualifies covering indexes.

Query JSONB through GIN and the @> operator

-- ❌ No index can serve this.
WHERE data->>'event_type' = 'click'

-- ✅ Served by a GIN index.
WHERE data @> '{"event_type": "click"}'

EXPLAIN symptoms and their fixes

Step 4: Verification

Once you've recommended a fix, always close the loop for the developer:

-- 1. Apply the change (new index, rewritten query, etc.)

-- 2. Re-run EXPLAIN to confirm the plan actually changed:
EXPLAIN (ANALYZE, BUFFERS) <new query>;

-- 3. Confirm the index is getting used:
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'your_table'
ORDER BY idx_scan DESC;

-- 4. Spot indexes that never get used (candidates to drop after a monitoring window):
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

Handy diagnostic queries

Bring these in when they fit the situation:

-- Slowest queries by mean time (needs the pg_stat_statements extension)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Foreign keys with no backing index
SELECT conrelid::regclass AS table, conname AS fk,
       a.attname AS column
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND c.conkey[1] = ANY(i.indkey)
  );

-- Rough table bloat estimate
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup,0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

How to talk to developers

• State the fix first, then the reasoning. They want to ship, not sit through a lecture.
• Put every SQL snippet in a code block — runnable, never pseudocode.
• Tag each problem plainly: 🔴 Critical / 🟡 Worth fixing / 🟢 Fine as-is.
• When several issues exist, rank by impact. Don't bury the real win under a list of ten nits.
• Recommending an index? Hand over the exact CREATE INDEX CONCURRENTLY statement.
• Rewriting a query? Show before and after, side by side.

Reference files

• references/explain-guide.md — reading an EXPLAIN ANALYZE plan node by node.
• references/index-patterns.md — deeper index patterns: partial, expression, covering, GIN/GiST/BRIN.

Pull these in when the question runs deeper than this main file covers.