EXPLAIN

QueryDiagnosticsOptimization

Stable On by default · production-ready

The four forms

EXPLAIN <statement>                  -- plan only, nothing runs
EXPLAIN ANALYZE <statement>          -- runs it, reports measured actuals
EXPLAIN VERBOSE <statement>          -- adds session and engine context
EXPLAIN SUGGEST <statement>          -- how to make this query faster

Options combine in any order: EXPLAIN ANALYZE VERBOSE SUGGEST SELECT ….

EXPLAIN — the plan

Prints the optimized logical plan, the routing tag (Nervous vs Reflex), which optimizer produced it, and the join strategy the executor would pick.

QueryPath: Reflex(reason=multi-table)
Optimizer: rules
JoinStrategy: JoinGraph(edge=10:0<->11:1, declared=yes, cached=yes)
Project: c.tier, o.id
  INNER Join ON (c.id = o.customer_id)
    Scan: customers AS c
    Scan: orders AS o

Plain EXPLAIN never executes the statement and never changes engine state — inspecting a plan does not warm a cache or count as a serve.

EXPLAIN ANALYZE — measured, not estimated

Runs the statement and reports what actually happened.

EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 100;

Execution: ACTUAL
  Rows: 412
  Plan+Build: 0.184 ms
  Open: 0.021 ms
  Pull rows: 3.907 ms
  Total: 4.112 ms

Bucket	Covers
Plan+Build	Planning, optimization, and physical-executor construction
Open	Pipeline start-up — hash-table builds, index probes
Pull rows	Draining every morsel to completion
Total	End to end

Rows is a true count, not a cardinality estimate.

Statements that modify data

EXPLAIN ANALYZE INSERT and EXPLAIN ANALYZE DELETE execute for real inside a transaction that is always rolled back, so the numbers are genuine and nothing persists:

Execution: ACTUAL (rolled back — no changes persisted)
  Rows affected: 2
  Total: 1.284 ms
  Note: sequence values consumed by SERIAL columns are NOT restored by the
  rollback (same as PostgreSQL).

Run these outside a transaction block.

What is currently refused

These return an explicit error rather than silently ignoring ANALYZE:

Statement	Why
DDL (CREATE TABLE, …)	Not journaled for undo, so it cannot be run and rolled back
UPDATE	An UPDATE that rewrites the column the undo journal keys on is not restored by ROLLBACK, so the measurement would persist
Any DML inside an open transaction	Containment would need a savepoint, and ROLLBACK TO currently unwinds the whole transaction

Use plain EXPLAIN for those. The last two restrictions are tracked and will lift once the underlying rollback behaviour is fixed.

Scan actuals and estimated-vs-actual

Below the totals, ANALYZE reports what each scan actually read, next to what the planner predicted:

Scan actuals (estimated -> actual):
  public.orders: 50 -> 50 rows
  public.customers: 2 -> 5000 rows   <-- ESTIMATE OFF (>4x)

The flag uses the engine’s own divergence threshold (4×) — the same predicate the adaptive executor uses to decide a plan is worth reacting to, so EXPLAIN never disagrees with it. An estimate of unknown means the planner had no statistic for that table; that is reported as missing, never as an accurate prediction and never flagged as a miss.

Two scope limits, stated plainly:

• Scans only. Joins, aggregates and sorts do not yet report cardinality, so this is a scan-level breakdown, not a full per-node profile. A plan with no scan says Scan actuals: none — this plan opened no instrumented scan.
• A full table scan predicts its own row count, so estimated and actual are equal by construction there. The comparison earns its keep on plans the planner expects to prune — point lookups, index lookups, Hilbert ranges — where a wrong prediction is exactly what you want surfaced.

There is no per-operator timing breakdown; the timing buckets are whole-query.

EXPLAIN SUGGEST — how to make it faster

Analyses the plan against the catalog and reports concrete, runnable changes. It is a static analysis: it inspects the plan and schema, it does not run the query. Combine with ANALYZE if you want both.

Why run it

It hands you DDL, not a hint. The headline case is a join running as a hash join because no graph index has been declared for it. SUGGEST names the exact join, and gives you the CREATE GRAPH INDEX statement that converts it into a Link-Stripe pointer hop — you paste it back and the advice flips to the earned all-clear.

The DDL is pre-validated, so it will not fail on you. The advisor checks what the CREATE GRAPH INDEX executor enforces before it suggests anything: BIGINT/TIMESTAMP key columns, single-column links, and a parent side proven unique from a PRIMARY KEY/UNIQUE constraint or a unique index. Uniqueness is proven from the catalog, never by scanning your table, so asking stays cheap on a large one.

One caveat on how a failed precondition is reported. A missing unique side is spelled out — you are told to add the constraint, and no DDL is offered. A non-BIGINT/TIMESTAMP key or a composite join key currently makes the suggestion silently absent instead: you still get the coverage verdict line, but nothing explains why no CREATE GRAPH INDEX appeared. If you expected a suggestion for a join and got none, check the key column’s type and arity first.

Asking is free. It never executes the query and never mutates engine state. The graph-index registry is read through a ledger-free peek, so inspecting a plan does not count as a serve, does not warm a fragment, and does not skew cache eviction — you can run SUGGEST across a whole workload without changing how that workload subsequently behaves.

It cannot give you a false all-clear. Every answer distinguishes assessed and clean from not assessed (see Reading the output honestly). “Nothing to report” is only ever printed when the check actually ran, and every gate that is switched off names itself in the output rather than contributing silence.

It surfaces signal the engine already had. The workload index advisor’s recommendations previously fed only the auto-create path and were never visible to a user; SUGGEST prints them, deduplicated against indexes that already exist, for the tables the query in front of you actually touches.

The four classes of advice

1. A join with no graph index

SUGGESTION: The join public.customers(id) = public.orders(customer_id) has no
graph index, so it runs as a hash join. Consider: CREATE GRAPH INDEX
gx_customers_orders ON LINKS ( public.customers(id) -> public.orders(customer_id)
CARDINALITY '1:N' ).

The parent side and cardinality are derived from which column carries a PRIMARY KEY/UNIQUE. If neither side is unique, you are told that instead of being handed DDL that would fail — a graph index requires a provably-unique parent.

2. A table missing from an existing cluster

SUGGESTION: Table public.shipnotes shares a JoinKey (customer_id) with the
active cluster but is missing from Graph Index 'gx_sales' (2/3 query tables
covered). Consider DROP GRAPH INDEX gx_sales and re-CREATE it including
public.shipnotes.

3. Secondary-index candidates

Surfaces the workload-driven index advisor for the tables this query touches, skipping any index that already exists.

4. Materialized-view candidates

Aggregate patterns seen often enough to be worth precomputing.

Not reachable on the shipped daemon yet

This class is gated on an auto-MV advisor that the packaged aetheriusd has no command-line switch for, so on a shipped server it always prints:

AUTO-MV ADVISOR: disabled — start the server with --enable-auto-mv-advisor to
collect aggregate patterns.

Do not pass that flag — aetheriusd rejects unknown arguments and will refuse to start. The other three classes are unaffected. This is a wiring gap being tracked, not a configuration mistake on your side.

Reading the output honestly

EXPLAIN SUGGEST distinguishes three states, and the difference matters:

Output	Meaning
SUGGESTION: <advice>	Something actionable was found
SUGGESTION: none — every joined table is covered…	Coverage was assessed and is complete
SUGGESTION: not assessed — <reason>	The check did not apply, e.g. no graph index is defined, or the query reads fewer than two tables

not assessed is never a clean bill of health. Likewise, the workload advisors report their own gate state:

INDEX ADVISOR: disabled — start the server with --enable-index-advisor to
collect predicate statistics and receive index suggestions here.

The two workload advisors have different gate states, and it is worth knowing which one you are looking at:

Advisor	Default
Secondary-index (class 3)	On for a daemon installed by the 0.1.97-or-later installer — the service definition renders --enable-index-advisor for you. Off for an older install, a hand-launched aetheriusd, or an embedded runtime; pass --enable-index-advisor or set AETHERIUS_ENABLE_INDEX_ADVISOR=1.
Auto-MV (class 4)	Off, with no switch to turn it on yet — see the note above.

Enabling the index advisor only makes it observe and recommend. Acting on its recommendations is a separate opt-in flag, --enable-autonomous-indexing.

Both advisors accumulate observations in-process, so their lists are empty on a freshly started server and reflect only the traffic seen since. An empty list means “nothing observed yet”, not “your schema is optimal” — which is why the enabled-but-quiet case prints its own line:

INDEX ADVISOR: enabled; no index candidate observed yet for this query's tables
(advice accumulates from predicates seen since server start).

SUGGEST applies to SELECT. On any other statement it says so rather than silently doing nothing.

EXPLAIN VERBOSE

Adds the session and engine context that shapes the plan:

Verbose:
  Default schema: public
  Optimizer: rules (rules on)
  JIT: off
  Morsel parallelism: on
  RLS: off

Graph Index & Nav-Serve →

Advanced Indexing (PTI) →

Adaptive Query Execution →