Studio edition /

Visual EXPLAIN

See the plan of a query as a tree with its costs, times and bad estimates, right from the SQL editor. Studio edition.

On this page

Run it#

In the Studio edition, switch to Advanced and open the SQL editor. Two buttons appear next to the usual ones:

  • Explain asks PostgreSQL for the plan without running the statement.
  • Explain analyze runs the statement to measure it, then undoes anything it changed.

They work on the statement under the cursor, or on the selection. It must be one statement; with several, Gresbase asks you to put the cursor in one or select only one.

Explain goes through the same path as a normal run, so it uses the tab's :name parameters, its timeout and its transaction mode.

ANALYZE doesn't change your data#

EXPLAIN ANALYZE really runs the statement. Gresbase wraps it so nothing stays:

  • Normally, in BEGIN … ROLLBACK.
  • In transaction mode, inside a savepoint that is rolled back afterwards, even if the statement fails. Your open transaction stays as it was.
  • On a read-only connection, in a read-only transaction: statements that write fail there.

The panel says so: “ANALYZE runs the statement to measure it. Anything it changed was rolled back: the data is as it was.”

Rolling back undoes the changes in the database, not their side effects: a sequence that advanced stays advanced, and a function that calls outside the database has already done it.

The plan#

The plan opens in a side panel, Query plan (or Query plan, executed with ANALYZE). On top: Execution and Planning times, and the total Cost.

The Tree shows one row per node:

  • The node type, such as Seq Scan, Hash Join or Update, and its target (table, index).
  • The estimated cost and, with ANALYZE, the time across all its loops and the number of loops.
  • Rows estimated against real rows. When they differ ten times or more, the row is marked off by ×N: a sign of stale statistics or a condition PostgreSQL can't estimate.
  • The share of the total spent in the node itself, with a bar: of the time with ANALYZE, of the cost without it. The most expensive node is marked.

Click a node to see all its properties under Node details: filters, index conditions, buffers, workers and the rest. Settings that differ from the defaults lists the planner settings changed from their defaults.

Text shows the plan in the text format of EXPLAIN. Copy text and Copy JSON copy it to the clipboard, for example to paste it into a plan visualizer or an issue.

Options by version#

Gresbase sends EXPLAIN (FORMAT JSON, SETTINGS) and adds ANALYZE and BUFFERS to Explain analyze. Plain Explain also gets BUFFERS from PostgreSQL 13, the first version that accepts it without ANALYZE.

In parallel plans, the times of nodes run by workers are the ones PostgreSQL reports per worker.