Azpg explain analyze

Reading query plans on Azure Database for PostgreSQL Flexible Server

A query plan is the optimizer's chosen strategy. Reading it well means finding the one node that dominates the time, understanding why the planner chose it (usually its row estimate), and deciding the fix: an index, a rewrite, updated statistics, or more memory.

This skill is read-only and auto-runnable. EXPLAIN builds a plan without executing; EXPLAIN (ANALYZE, BUFFERS) does execute the query — safe for SELECT, but for INSERT/UPDATE/DELETE it performs the write, so wrap those in a transaction you roll back (see Safety protocol). It never creates indexes or changes config — for that, hand off to azpg-index-selection or azpg-config-tuning.

Deep node-by-node reference is in reference.md; the Flexible-Server timing caveats (managed I/O, shared buffers vs storage, remote latency) are in azure-constraints.md.

Live instance context (dynamic injection)

PGCONN is a libpq connection string. All read-only.

• Postgres major version (plan/option availability): !psql "$PGCONN" -tAc "SHOW server_version;" 2>/dev/null || echo "(unknown — ask for connection details)"
• Memory that governs sort/hash spills (Sort Method: external merge = spilled): !psql "$PGCONN" -tAc "SHOW work_mem;" 2>/dev/null || echo "(unknown)"
• Tables with stale stats (autovacuum hasn't analyzed recently → bad estimates): !psql "$PGCONN" -tAc "SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_mod_since_analyze DESC NULLS LAST LIMIT 5;" 2>/dev/null || echo "(unknown)"

When to use this skill

Trigger on: "why is this query slow?", "what does this EXPLAIN mean?", "read this plan", "is this using my index?", "why a seq scan / nested loop?", "the estimate is way off", "is it spilling to disk?", "interpret BUFFERS". If the conclusion is "add an index" hand off to azpg-index-selection; if it's "raise work_mem / tune the server" hand off to azpg-config-tuning; for aggregate slow-query forensics across the whole workload use azpg-stat-diagnostics.

Decision flow

All steps are read-only.

1. Capture the real plan. Run EXPLAIN (ANALYZE, BUFFERS) — never optimize from EXPLAIN alone (estimates ≠ reality). Use scripts/explain_query.sql for the safe rollback wrapper on writes.
2. Find the dominant node. Read inside-out / bottom-up; find where actual time × loops concentrates. That node is the target — ignore cheap ones.
3. Check the estimate. Compare rows= (estimated) vs actual rows. A large gap (e.g. 10×+) means the planner is flying blind → stale stats, correlated predicates, or a non-sargable expression.
4. Classify the problem using the table below and reference.md.
5. Route the fix — index (azpg-index-selection), rewrite (here), stats (ANALYZE), or memory/config (azpg-config-tuning).

Symptom → likely cause → fix

In the plan	Likely cause	Fix / route
Seq Scan on a big table with a selective filter	no usable index, or predicate not sargable	index it → azpg-index-selection; make predicate sargable (below)
rows estimate ≫ or ≪ actual rows	stale statistics / correlated columns	ANALYZE <table>; consider CREATE STATISTICS for correlations
Nested Loop with high loops on inner side	planner underestimated outer rows	fix the estimate (stats); an index on the inner join key helps
Sort Method: external merge Disk: …kB	work_mem too low for this sort	raise work_mem (session or server) → azpg-config-tuning
Hash Batches: >1 / Disk Usage on Hash	hash spilled — work_mem too low	same as above
Rows Removed by Filter: large	reading rows then throwing them away	index the filter, or a partial index
Heap Fetches: high on an Index Only Scan	visibility map stale	VACUUM <table>
Index Scan but still slow	low selectivity or wide heap fetches	covering index, or accept the scan
Big gap between planning time and execution	many partitions / complex plan	check partition pruning, generic vs custom plans

Making a predicate sargable (common rewrites)

The planner can only use an index when the indexed column appears bare on one side. Wrapping it in a function or arithmetic disables the index unless a matching expression index exists.

Non-sargable	Sargable rewrite
WHERE date(created_at) = '2024-01-01'	WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
WHERE lower(email) = $1	keep it, but add an expression index ON t (lower(email))
WHERE col + 0 = $1 / WHERE col * 2 > $1	move arithmetic to the constant side
WHERE col LIKE '%foo%'	trigram GIN (pg_trgm) → azpg-index-selection
WHERE col::text = $1	compare in the column's native type

Reading BUFFERS

shared hit = pages served from PostgreSQL's shared buffers (fast, in memory). shared read = pages fetched from storage. On Flexible Server, read pages come from managed remote storage, so a high read count with low hit is a strong slowness signal — the node is I/O-bound. See azure-constraints.md for why local timing intuition can mislead on managed storage.

EXPLAIN options cheat-sheet

• ANALYZE — actually run it, show real times/rows. Executes writes.
• BUFFERS — page hits/reads/dirtied; pair with ANALYZE.
• FORMAT JSON — machine-readable, good for diffing plans.
• SETTINGS — show non-default planner GUCs in effect (PG12+).
• WAL — WAL volume for write statements (PG13+).
• VERBOSE — output columns and schema-qualified names.

Safety protocol

1. Reads run automatically. EXPLAIN and EXPLAIN (ANALYZE, BUFFERS) on a SELECT change nothing.
2. ANALYZE executes the statement. For INSERT/UPDATE/DELETE, never run a bare EXPLAIN ANALYZE. Use the rollback wrapper:

   BEGIN;
   EXPLAIN (ANALYZE, BUFFERS) UPDATE ...;
   ROLLBACK;
   

   scripts/explain_query.sql does exactly this.
3. Don't mutate to "fix" it here. This skill diagnoses. Index/config/DDL changes are handed to the write skills, which apply their own guarded, dry-run flow.
4. ANALYZE <table> (statistics refresh) is a safe, non-destructive maintenance command — recommend it freely when estimates are stale.

Bundled files

• reference.md — every scan/join/aggregate/CTE node explained, the estimate-vs-actual method, and worked plan walkthroughs.
• azure-constraints.md — managed-storage timing, shared_buffers sizing by tier, remote-latency effects on plans.
• scripts/explain_query.sql — read-only; safe rollback wrapper for EXPLAIN ANALYZE on writes.
• scripts/find_slow_patterns.sql — read-only; surfaces tables with stale stats and high seq-scan ratios to explain why plans go wrong.
• examples/reading_a_plan.md — an annotated slow-plan walkthrough, from symptom to routed fix.