Execution plans

The Execution plans section of a query page keeps every distinct plan the planner has chosen for that query, so you can see when the plan changed and what changed.

Where plans come from

Source	How	Kind
Automatic capture	The agent periodically plans the busiest SELECT statements.	Estimated (generic plan)
Capture now	Click Capture plan; the agent picks the request up within seconds.	Estimated (generic plan)
Upload	Paste EXPLAIN (FORMAT JSON) or EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output.	Estimated, or Executed when ANALYZE was used

Automatic capture

Requires PostgreSQL 16+ (for EXPLAIN (GENERIC_PLAN)) and a readable pg_stat_statements. On each round (every 10 minutes by default), the agent:

1. Reads the top statements of the observed database by total execution time (over-fetching so that non-SELECT and monitoring statements can be skipped).
2. Keeps up to queries per round (20 by default) that are a single SELECT, not a query against statistics views.
3. Runs EXPLAIN (FORMAT JSON, GENERIC_PLAN) for each, inside one READ ONLY transaction with a 100 ms lock timeout and a 2 s statement timeout, with a savepoint per statement so one failure does not abort the rest. The transaction is always rolled back.
4. Sanitizes each plan on the observed host, then sends it with the regular telemetry.

GENERIC_PLAN plans the statement with its $1 placeholders unbound: nothing is executed. Enable, disable or tune capture per database in Database settings.

Capture now

Capture plan queues a request. The agent polls for work every 5 seconds, captures the plan the same safe way, and reports back. The request moves through PENDING → IN_PROGRESS → COMPLETED (or FAILED with a reason). A request no agent picks up within 5 minutes becomes EXPIRED, and one claimed but not reported within 5 minutes is marked FAILED.

Uploading a plan

Upload plan accepts the JSON output of EXPLAIN, with an optional note (up to 500 characters). Plans up to 1 MB of JSON and 10,000 nodes are accepted. Literal values in conditions are stripped before the plan is stored, just like query text. To get measured timings, run on a safe environment:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;

EXPLAIN ANALYZE executes the query, including any writes it performs. Run it yourself, where that is acceptable.

Plan versions

A plan is fingerprinted by its structure: node types, relations, aliases, indexes, join types, strategies, scan direction and child order. Costs, row estimates, timings, worker counts and expression text are excluded. As a result:

• A new version is recorded only when the planner made a different decision.
• Capturing the same structure again just updates that version’s last seen time.
• An agent’s generic plan ($1) and an uploaded plan (?) with the same shape share a hash.

The versions list is newest first. A violet ▲ changed separator marks where the structure changed. Each row shows when it was captured, the short hash, badges (Current, Agent or Uploaded, Estimated or Executed, and Seq scan when the plan contains one), the relations it reads, and node count, estimated cost and execution time (executed plans only).

Reading a plan

Selecting a version shows a header (capture and last-seen times, source, node count, estimated total cost and rows, planning and execution time (executed plans only), PostgreSQL version, relations and node-type counts) and the plan tree.

• The chevron folds a node’s children; clicking a node opens its details: filter, index, join, hash, merge and recheck conditions, sort and group keys, sort method and space, workers, buffers and any other properties. Expand all / Collapse all and Raw JSON are available.
• Every node shows estimated rows and cost (startup..total).
• For executed plans, nodes also show actual rows × loops, self time (time in the node itself, excluding children) and its share of total time. The three nodes with the largest self time are emphasised.

Estimated plans have no timings at all. QueryPilot shows them as “not executed”, never as zero.

Warning chips

Chip	When
Seq scan · ~N rows est.	A sequential scan the planner expects to return at least 10,000 rows.
estimate off ×N	Executed plans: actual rows differ from the estimate by 10× or more, often due to stale statistics.
spilled to disk	A sort did not fit in work_mem and wrote temporary files.

Comparing plans

Switch to Compare and pick a base (before) and target (after) version (by default the previous and current ones). ⇄ Swap reverses them.

The comparison is semantic, not a line-by-line tree diff (one inserted Sort would otherwise shift every node below it). It reports the changes that explain regressions:

Change	Example
Access method changed	public.orders: Index Scan using orders_customer_id_idx → Seq Scan
Index changed	orders now uses index orders_created_at_idx instead of orders_customer_id_idx
Join strategy changed	Join strategy changed from Nested Loop to Hash Join
Node added / removed	Sort added. · public.customers is no longer read
Estimate changed	Root row estimate moved by 10× or more

If the two versions have the same structure the result is Identical structure. When one plan is estimated and the other executed, a note explains that only the executed plan has measured timings. Summary cards for both versions show nodes, estimated cost and rows, whether there is a sequential scan, execution time and relations.