EXPLAIN / EXPLAIN ANALYZE with BUFFERS, WAL, TIMING, SETTINGS, and JIT

Learning outcomes

ServiceHub's team now has a plan, but the next failure is subtler: someone reads estimated rows=500 as if those rows were observed, another runs EXPLAIN ANALYZE DELETE ... against valuable data, and a third compares timing numbers produced with different instrumentation settings. This lesson turns EXPLAIN into a controlled measurement workflow.

1. Recreate a compact planner lab

DROP TABLE IF EXISTS app.ch11_explain_lab;CREATE TABLE app.ch11_explain_lab (    id bigint PRIMARY KEY,    tenant_id integer NOT NULL,    status text NOT NULL,    payload text NOT NULL,    amount numeric(12,2) NOT NULL);INSERT INTO app.ch11_explain_labSELECT g,       1 + (g % 50),       CASE g % 5 WHEN 0 THEN 'queued' WHEN 1 THEN 'assigned' WHEN 2 THEN 'done' WHEN 3 THEN 'cancelled' ELSE 'waiting' END,       repeat(md5(g::text), 4),       ((g % 100000) + 100)::numeric / 100FROM generate_series(1,180000) AS g;CREATE INDEX ch11_explain_tenant_status_idxON app.ch11_explain_lab (tenant_id, status) INCLUDE (amount);ANALYZE app.ch11_explain_lab;

2. Read estimates before actual execution

EXPLAIN (SETTINGS)SELECT tenant_id, sum(amount)FROM app.ch11_explain_labWHERE tenant_id BETWEEN 5 AND 12  AND status IN ('queued','assigned')GROUP BY tenant_idORDER BY tenant_id;

At each node, rows means estimated rows emitted by that node if it runs to completion—not rows scanned. Cost values describe the optimizer's model. The top node's total cost is the estimated cost to produce the complete result. With LIMIT or an early-stopping parent, the parent can account for only a fraction of a child's completion cost.

3. Overlay actual rows, loops, and buffers

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY)SELECT tenant_id, sum(amount)FROM app.ch11_explain_labWHERE tenant_id BETWEEN 5 AND 12  AND status IN ('queued','assigned')GROUP BY tenant_idORDER BY tenant_id;

actual rows is reported per loop. When a node has many loops, total tuples processed by that node are approximately actual-rows-per-loop multiplied by loops. Do not subtract parent and child execution times as if each node's reported time were an isolated stopwatch; node times include their descendants unless you reason carefully about the tree.

BUFFERS shows shared/local/temp buffer hits, reads, dirties, and writes. A buffer “hit” means the page was found in PostgreSQL's buffer cache; it does not by itself prove the operating system or physical storage was irrelevant to the broader workload.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT tenant_id, sum(amount)FROM app.ch11_explain_labWHERE tenant_id BETWEEN 5 AND 12  AND status IN ('queued','assigned')GROUP BY tenant_idORDER BY tenant_id;

4. TIMING can add measurement overhead

With ANALYZE, node-level timing is on by default. Repeatedly reading the system clock can be measurable on some platforms, especially for plans with many tiny node invocations. Use TIMING OFF when row counts, loops, buffers, and overall execution time are sufficient.

SELECT name, setting, unitFROM pg_settingsWHERE name IN ('track_io_timing','track_wal_io_timing','jit','jit_above_cost','jit_inline_above_cost','jit_optimize_above_cost')ORDER BY name;

track_io_timing can enrich buffer evidence with I/O timing, but it has platform-dependent timing overhead and is disabled by default on many installations. Do not turn it on globally merely because a tutorial uses it.

5. EXPLAIN ANALYZE executes DML—prove it safely

This is the most important operational rule in the chapter. EXPLAIN ANALYZE does not simulate writes. It executes them. The safe learning pattern is an explicit transaction followed by ROLLBACK.

BEGIN;SELECT count(*) AS queued_beforeFROM app.ch11_explain_labWHERE tenant_id = 7 AND status = 'queued';EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, TIMING OFF)UPDATE app.ch11_explain_labSET status = 'assigned'WHERE tenant_id = 7  AND status = 'queued';SELECT count(*) AS queued_inside_transactionFROM app.ch11_explain_labWHERE tenant_id = 7 AND status = 'queued';ROLLBACK;SELECT count(*) AS queued_after_rollbackFROM app.ch11_explain_labWHERE tenant_id = 7 AND status = 'queued';

WAL reports generated WAL records, full-page images when present, bytes, and buffer-full events, but only when ANALYZE is enabled. The exact WAL volume depends on page state, checkpoint history, indexes, wal settings, and server version; do not hard-code expected byte counts.

6. SETTINGS records planner-relevant nondefaults

BEGIN;SET LOCAL random_page_cost = 1.2;SET LOCAL enable_bitmapscan = off;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT id, amountFROM app.ch11_explain_labWHERE tenant_id = 9  AND status = 'assigned';ROLLBACK;

SETTINGS reports configuration parameters affecting query planning whose values differ from built-in defaults. This is valuable when a copied plan otherwise hides the context that made it possible. It is not a dump of every GUC.

7. JIT is conditional, not guaranteed

Just-in-Time (JIT) compilation turns parts of expression evaluation and tuple deforming into native code when PostgreSQL is built with an available JIT provider, jit is enabled, and estimated cost crosses configured thresholds. JIT often helps long CPU-bound analytical work more than short OLTP queries because compilation itself costs time.

SHOW jit;SHOW jit_provider;SHOW jit_above_cost;SHOW jit_inline_above_cost;SHOW jit_optimize_above_cost;

BEGIN;SET LOCAL jit = on;SET LOCAL jit_above_cost = 0;SET LOCAL jit_inline_above_cost = 0;SET LOCAL jit_optimize_above_cost = 0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT sum((amount * amount)::numeric)FROM app.ch11_explain_labWHERE md5(payload) IS NOT NULL;ROLLBACK;

If the server was built without an available JIT provider, no JIT section will appear. If it does appear, compare compilation overhead with execution savings on a representative workload before considering any threshold change.

8. PostgreSQL 18 EXPLAIN adds useful context

PostgreSQL 18 supports MEMORY to report planner memory use and SERIALIZE to measure output serialization work during ANALYZE. These are useful when the diagnostic question is planning-memory pressure or the gap between executor work and result conversion.

EXPLAIN (MEMORY, SETTINGS)SELECT tenant_id, status, avg(amount), count(*)FROM app.ch11_explain_labGROUP BY tenant_id, statusORDER BY tenant_id, status;

EXPLAIN (ANALYZE, SERIALIZE TEXT, BUFFERS, TIMING OFF)SELECT id, tenant_id, payloadFROM app.ch11_explain_labWHERE tenant_id BETWEEN 1 AND 10;

Even with serialization enabled, EXPLAIN does not send the result rows to the client. Network latency and client rendering still require separate measurement.

9. Production judgment and cleanup

A useful plan capture states whether it is estimate-only or executed, records PostgreSQL version, query parameters, settings, statistics freshness, buffer/timing instrumentation, and whether cache state is controlled. For DML, explicitly state that the measurement ran on a disposable environment or was rolled back.

DROP TABLE IF EXISTS app.ch11_explain_lab;

Authoritative references

Planner and executor behavior is version-sensitive and workload-sensitive. Verify the exact PostgreSQL major, statistics state, server settings, indexes, and data distribution before generalizing any plan shown in this lesson.

• PostgreSQL 18 — EXPLAIN
• PostgreSQL 18 — Using EXPLAIN
• PostgreSQL 18 — Runtime Statistics
• PostgreSQL 18 — Just-in-Time Compilation
• PostgreSQL 18 — When to JIT
• PostgreSQL 18 — Release 18 Notes