pgconfigurator
pgconfigurator
Talk to sales

Plan analysis guide

EXPLAIN output is dense. This guide is a short tour of the parts that matter, the patterns we look for, and the fixes that usually work.

How to capture a plan

The tool accepts both text and JSON. Text is what psql emits by default; JSON is best for tooling because of its precision.

-- text (default)
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- JSON (recommended for sharing)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Always include ANALYZE when you can — it adds actual timings and row counts. Always include BUFFERS when you can — it tells you where time went (disk vs cache).

Anatomy of a plan

Each node has four numbers worth reading:

  • cost— planner's estimate. Two values (startup..total) in abstract units. Useful for comparing alternatives, not for absolute judgment.
  • actual rows / loops — what really happened. A wide gap between estimated and actual is the single most common cause of bad plans.
  • actual time — wall-clock, per loop. Multiply by loops to get the total contribution.
  • buffers (with BUFFERS) — shared hit vs read. Reading from cache is fast; reading from disk is slow. Temp reads mean a spill.

What we detect automatically

Sequential scan over a large table with a filter

Classic missing-index story. The scan reads everything, the filter throws most of it away. The fix is almost always a supporting index aligned with the filter columns.

Bad row estimates (estimate off by 10× or more)

The planner can't pick a good plan with bad numbers. ANALYZE refreshes stats; CREATE STATISTICS helps when multi-column correlations matter (e.g. state = 'CA' AND city = 'SF').

Sort or hash spilled to disk

Spills happen when the operation exceeds work_mem. The cure is almost always SET LOCAL work_mem = '128MB' for the query — not bumping the global. The global affects every connection and every sort/hash simultaneously, which compounds badly.

Nested loop rescan

Inner side scanned thousands of times. Usually traces back to a row underestimate on the outer side. Fix the estimate first; the planner will pick hash or merge join instead.

Lossy bitmap heap scan

When a bitmap doesn't fit in work_mem, PostgreSQL stores block-level bits instead of tuple-level — each match then needs a row-by-row recheck. Raise work_mem locally.

Index Only Scan with heap fetches

The visibility map said “not all visible”, forcing PostgreSQL to read the heap anyway. That defeats the speed advantage. Aggressive autovacuum on the table keeps the VM up-to-date.

What we don't (yet) detect

Lock waits, generic-vs-custom plan thrashing, partition pruning misses, parallel worker contention beyond “launched < planned”. These are on the roadmap. If you suspect one and want a second pair of eyes, talk to CYBERTEC engineers.