Explain a query

Explain shows the plan PostgreSQL would use for a statement, from the planner’s estimates, without running it. Explain Analyze runs the statement, rolls it back, and shows the real time and rows of every step. Both open in the Explain tab of the Query Tool, beside Data Output and Messages.

Explain a statement

Put the cursor in the statement, or select it, then:

To get	Toolbar	Menu	Mac	Windows and Linux
The plan with the planner’s estimates	Explain	QueryExplain	`⌘ E`	`F7`
The plan with real times and rows	More (…) › Explain Analyze	QueryExplain Analyze	`⌥ ⌘ E`	`Shift+F7`

pgjumbo explains one statement: the selection if there is one, else the statement the cursor is in (or the last one before it, when the cursor is between statements; the first one, when the cursor is before them all). With nothing in the editor, nothing happens. It sends your statement to the server behind EXPLAIN (FORMAT JSON), or EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) for Explain Analyze, on the tab’s own connection, so the tab’s session settings (search path, role, work_mem) apply. There are no other options. For VERBOSE, SETTINGS or WAL, type the EXPLAIN yourself and run it: its plan comes back as rows in Data Output.

Explain is greyed out while the tab is not connected, while it runs something, and in a data tab. Stop in the toolbar (⌘ . (Ctrl+. on Windows and Linux)) cancels an Explain that takes too long, as it cancels a query.

While the plan is on its way, the result bar says Explaining… or Explain Analyze…, and the tab says “Asking the planner…” or “Running the statement for its plan (it is rolled back)…”. The result bar then says Explain or Explain Analyze and how long it took. The Explain tab keeps the last plan until you explain again; running the script does not clear it.

What Explain Analyze changes

Explain Analyze really runs the statement, so it takes the time, the locks and the server’s resources the statement takes. pgjumbo runs it inside BEGIN and ROLLBACK, so an INSERT, UPDATE or DELETE leaves the data as it was. When the tab has a transaction open (manual commit), pgjumbo uses a savepoint instead and rolls back only to it, so your transaction stays as it was. Work that a rollback cannot undo still happens, for example values taken from a sequence.

If the tab’s transaction has failed, Explain Analyze says “This tab’s transaction failed. Roll it back before explaining.” Roll back first (see Transactions and sessions).

Read the plan as a table

The Table view is where the Explain tab opens. Each step of the plan is a row, indented under the step it feeds.

The Explain tab after Explain Analyze of a query joining orders and customers: six plan steps with bars for their share of the time, their own time and their rows against the estimate; the index scan on orders is highlighted, and a hint under the table says the planner expected 2,140 rows but got 195.

1 The Explain tab.
2 How to show the plan: Table, Diagram, Text or JSON. Each query tab keeps its choice.
3 For Explain Analyze, the plan’s planning time, its execution time and its shared buffers (hit were found in memory, read came from disk or the operating system’s cache).
4 The slowest step is highlighted: the one with the most time of its own (or, without Analyze, the most cost of its own).
5 Rows the step returned against the rows the planner expected. With Explain Analyze, pgjumbo marks the number in another colour when the two differ more than ten times either way (and one of them is at least 10): the table’s statistics may be out of date.
6 The hint names the slowest step and, where it can, says what to try.
7 Ask about this plan opens the Assistant and asks “Why is this query slow, and what would make it faster? Use its plan.”, with the plan attached. It shows only while the Assistant is turned on; see SQL help in the editor.

The table’s columns:

Column	With Explain Analyze	With Explain
Node	The step as `EXPLAIN` names it (`Hash Join`, `Index Scan`, `HashAggregate`) and what it works on: a table and its alias, an index, a join condition, a sort key. Hover over it to read all of it, with its filter, the rows its filter removed and its loops.	The same.
Share of time / Share of cost	A bar for the step’s own time as a share of the whole plan.	A bar for its own cost as a share of the whole plan.
Self / Cost	The step’s own time over all its loops: its total time less the time of the steps under it.	Its own cost: its total cost less that of the steps under it.
Rows / est. / Rows (est.)	Rows returned, over all its loops, against the estimate.	The rows the planner expects.

The hint under the table says one of these:

“Slowest step: the index scan on orders, 82 % of the time.” (of the cost, without Analyze)
When the slowest step was badly estimated: “Slowest step: the index scan on orders. The planner expected 2,140 rows but got 195; running ANALYZE on orders may improve the plan.”
With Explain Analyze, when the slowest step reads a table and its filter threw away at least 1,000 rows and more than ten times the rows it kept: “Slowest step: the seq scan on orders (82 % of the time). Its filter threw away 98,000 rows to keep 120; an index on the filtered columns may help.”

Copy JSON in the result bar copies the plan as the server wrote it, for tools that draw plans. In the Text view the button is Copy text and copies the plan as text. The result bar then says “Copied the plan”. Both buttons drop their words in a narrow results pane.

Without Analyze

The Explain tab after a plain Explain: the line says these are the planner's estimates, and the columns are Share of cost, Cost and Rows (est.).

1 The result bar says Explain and the time it took.
2 “The planner’s estimates; Explain Analyze (Shift+F7) runs the statement for real times.” On a Mac it names ⌥⌘E.
3 Cost is each step’s own cost, in the planner’s units.
4 Rows (est.) is the number of rows the planner expects. Nothing is marked as badly estimated, since nothing ran.

Read the plan as a diagram

Click Diagram. pgjumbo draws each step as a card: the top of the plan on the left, the steps that feed it to the right. Rows flow from right to left, and a thicker line carries more rows.

The plan as a diagram, scrolled to the right: cards for HashAggregate, Hash Join, Index Scan and Hash joined by lines, the Index Scan card outlined, and its details on the right: share of time, actual time, rows, estimated rows, buffers, cost, the index condition and a hint.

1 A step’s card: its name and what it works on, a bar for its share of the time (or cost), its own time (or cost), and its rows against the estimate (195 of 2,140; with Explain only the estimate). The slowest step has an outline and is chosen when the diagram opens. Click any card to choose it.
2 The lines join each step to the step it feeds. The more rows a step returns, the thicker its line.

3 The chosen step’s name and what it works on, then its numbers:

Line	What it says
Share of time / Share of cost	Its own time (or cost) as a share of the plan; in another colour for the slowest step.
Actual time	Explain Analyze only: the time to its first row and to its last, per loop.
Rows	Explain Analyze only: the rows it returned, over all loops.
Estimated rows	The rows the planner expected; in another colour when ten times off.
Loops	How many times it ran, when more than once.
Buffers	Explain Analyze only: shared buffers `hit` and `read`, the steps under it included.
Cost	The planner’s cost to its first row and to its last.

4 Its conditions and keys, in full: Index condition, Recheck condition, Hash condition, Merge condition, Join filter, Filter, Sort key and Group key, as the step has them.
5 A hint: for the slowest step, the same as under the table; for another badly estimated step, “The planner expected 2,140 rows here and got 195; running ANALYZE on orders may give it better numbers.”

A plan wider or taller than the pane scrolls, as in the picture, where the first card (Sort) is off to the left. The line under the cards repeats how to read them: “Rows flow from right to left; a thicker line carries more rows. Click a step to see its details.”

Read the plan as text or JSON

Text shows the plan laid out the way psql prints EXPLAIN: each step with its cost (and with Analyze its time, rows and loops), its keys, conditions and the rows its filter removed under it, and the steps it reads from indented behind an arrow (->). pgjumbo writes this text from the JSON plan. Copy text copies it.
JSON shows the plan exactly as the server sent it (FORMAT JSON). Copy JSON copies it, for example to paste into a plan visualiser.

When there is no plan

The Explain tab with no plan: the result bar says No plan in red and the tab says Explain one statement at a time: put the cursor in it, or select it.

1 The result bar says No plan in red.
2 The tab says why. The messages you may see:

Message	What to do
“Explain one statement at a time: put the cursor in it, or select it.”	The selection holds more than one statement. Select one, or click into it with nothing selected.
“There is no statement to explain.”	The selection holds only comments or blank lines. Select the statement itself.
“A psql meta-command cannot be explained.”	The statement is a backslash command such as `\d`.
“This tab’s transaction failed. Roll it back before explaining.”	A statement failed inside your transaction. Click Rollback.
“This tab is still running a query. Wait for it, or cancel it.”	The tab is busy. Wait, or click Stop.
“This tab’s connection is busy. Try again in a moment.”	Explain Analyze found the connection neither idle nor in a transaction. Try again.
An error from the server, such as `relation "orders2" does not exist`	The statement itself has an error. Fix it as you would before running it.