Find out why something is slow

Start with the query you know is slow and explain it. When you don’t know which query it is, Top statements shows where the time goes, and Health lists what needs maintenance.

Explain a query

1. Put one statement in a query tab. Explain reads the whole tab, so a tab with a script says Explain one statement at a time.
2. Click Explain on the run bar, or press ⌘⌥E on macOS or Ctrl+Alt+E on Windows and Linux.

The plan replaces the result. Its bar reads Plan · estimated cost 1,234 · nothing ran: without Analyze, Postgres only plans the statement, and the numbers are the planner’s estimates.

Alcuin sends your statement behind its own prefix:

EXPLAIN (FORMAT JSON, VERBOSE) SELECT …

If the tab showed a result before, it stays behind the plan. Close the plan, or press Esc in it, to go back to the result.

Measure it with Analyze

Open Explain options, the arrow beside Explain, and turn on Analyze. Then click Explain. Changing an option only chooses it: the arrow says Options changed: Explain to apply until you explain again.

With Analyze, Postgres runs the statement and reports real times and row counts. The bar reads Plan · 41.2 ms to run · 0.3 ms to plan.

Analyze always runs inside a transaction that Alcuin rolls back:

BEGIN READ ONLY;  -- BEGIN, for anything but a plain SELECT
EXPLAIN (FORMAT JSON, VERBOSE, ANALYZE) SELECT …;
ROLLBACK;

Inside a transaction you opened yourself, Alcuin uses a savepoint instead and rolls back to it. For anything but a plain SELECT, the plan says Analyze ran this statement inside a transaction that was rolled back, so nothing it changed was kept. The statement still runs for real while it is measured: it takes its locks and does its work, and your Safety rules ask first as they would for a run. On a read-only connection, Analyze is refused for anything but a plain SELECT. See ALC-3001.

The palette command Explain this query with Analyze turns Analyze on for the tab and explains in one step.

The other options

Option	Adds
Buffers	Blocks each node read from cache and from disk
Settings	Planner settings that differ from the defaults
WAL	WAL records each node wrote. Needs Analyze, and turns it on
Generic plan	A plan with $1 parameters left unbound. Needs Postgres 16 or later

Generic plan and Analyze exclude each other: turning one on turns the other off.

Read the plan

The Plan tab draws the plan as a tree: the node that returns the rows at the top, and the nodes it reads from nested under it. Fold a node to hide its children.

Each node shows:

• Its name and conditions: the node type, the relation, and lines such as its filter or join condition.
• Rows: the planner’s estimate. After Analyze, the estimate and then the actual rows. When the two are far apart, a badge says by how much, such as ×9.7 under. A large misestimate often means the table’s statistics are out of date.
• Own cost and Total cost before Analyze. Own time and With children after it. Own time is the node’s time without the nodes under it, with a bar for its share of the whole.
• A hot-spot badge on the nodes that take the most: 62% of cost before Analyze, 62% of time after it.
• Loops and buffers, when the node ran more than once or Buffers is on.

123

A plan after Analyze.

1. 1The hot-spot badge.
2. 2How far the estimate missed.
3. 3Own time, with a bar for the node's share.

Click a node to highlight the relation it reads in your SQL.

JSON shows the plan as Postgres returned it, and Text lays it out as EXPLAIN’s text format does. Both have a copy button.

Act on a slow scan

A sequential scan with a filter offers two actions:

• Index this filter stages an index on the filter’s columns in the table’s Structure review. Nothing is built until you review and apply it. Once it is applied, the tab explains again and compares the new plan with the old one.
• Try without building explains the statement as if that index existed, using the HypoPG extension. Nothing is built and nothing runs. Without HypoPG in the database, it says so beside the button.

Compare plans

Explain the same SQL again with the same options and the new plan is compared with the one before. Nodes are marked new, changed, gone or moved, and changed numbers show how much they moved. Choose another earlier plan in Compare with.

Explain from elsewhere

• A table’s Data tab: the explain button on the toolbar, Explain the statement this page ran, explains the exact statement the page ran, with its filters and sort. The plan opens in the Plan tab of the bottom panel.
• History: each entry has Explain, with the values it ran with.
• Activity: a session’s Actions menu has Explain for what it is running. See Find what is stuck.
• Top statements, below.

Find the statements that take the time

Choose Show top statements in the command palette, or Top statements on Home.

Top statements reads the pg_stat_statements extension for this database. It lists up to 200 statements by total time, with Calls, Total time and its share, Mean, Rows, Blocks hit, Blocks read and Role. Sort by any of the number columns.

The counts run from the last time the statistics were reset, so old work can hide new. Take a snapshot to count only what runs from now on. The view then shows Showing only what changed since the snapshot: calls, time, rows and blocks since then. Show all time goes back.

Each statement’s Actions menu has Explain, Open in a query tab and Copy statement. pg_stat_statements replaces constants with $1, $2, so such a statement is explained as a generic plan, which needs Postgres 16 or later. On an older server it opens in a new query tab instead, with Not explained: it needs Generic plan (Needs PostgreSQL 16). Fill in its parameters to explain it.

A table’s Overview also lists the top statements that name it, with the same Explain.

When the extension is missing

Top statements says what is missing and shows the SQL to fix it:

• pg_stat_statements is not installed in this database: the server loads it, but the database has not created it. Create extension… runs CREATE EXTENSION IF NOT EXISTS pg_stat_statements after asking, on an unlocked connection. It needs a superuser or the database owner.
• The server does not load pg_stat_statements: it must be added to shared_preload_libraries and the server restarted. Copy the steps for whoever runs the server.

Check Health

Choose Show health in the command palette, or open it from Home. Health lists findings for your database and its server, worst first, grouped as Autovacuum, Vacuum and bloat, Statistics, Indexes, Sessions and Sequences. Each is marked needs attention, worth a look or note, and Server or This database.

Health flags, among others:

• Tables with many dead rows, or that autovacuum has fallen behind on.
• Tables never analyzed, or with many rows changed since the last analyze. Both mislead the planner.
• Indexes that are invalid, duplicated, covered by a wider index, or never scanned.
• Sessions idle in a transaction, or running one long transaction.
• Sequences past half their range.

Each finding’s Actions menu has its fix and Copy statement:

• Vacuum…, Analyze… and Reindex… ask first, with the statement and what it locks, then run.
• Stage drop on an index stages DROP INDEX CONCURRENTLY in the table’s Structure review.
• Terminate session… on a session acts as in Activity.
• Stage: autovacuum back on, and Stage: bigint… on a sequence near its end, stage the change in the table’s Structure review.

Fixes that write are hidden while the connection is read-only. Copy statement is always there.

If it did not work

• This connection is read-only, and Analyze would run this statement. Unlock it, or explain without Analyze.: see ALC-3001.
• This is already an EXPLAIN: run it as a query instead.: remove the EXPLAIN from the tab, or run it.
• A generic plan needs PostgreSQL 16.: turn off Generic plan, or replace the $1 parameters with values.
• Analyze takes as long as the query: it runs the query. Your statement time limit still applies; see Work on production without breaking it.