Query performance

Query Performance ranks what your database spends its time on, one row per query shape. From any row, you can read the query’s plan, ask an agent for a recommendation, and test an index on a clone.

Open a database and choose Queries. The page has three tabs:

Tab	What it is for
Queries	Ranked queries in your workload
Optimize	Submit a query to an agent for a recommendation
Explain Plan	Run EXPLAIN against the live database and read the plan

Turn it on

The ranked list comes from pg_stat_statements. If it is not loaded, or the collector cannot read it, the page is empty. Load pg_stat_statements and allow the collector to read it. Empty pages has the full fix.

The other tabs on a query’s detail view have their own conditions:

• On a query’s detail view, Explain Plan needs a PostgreSQL component. Running a new plan also needs a collector that is online and allows the explain command. If the collector is offline or does not allow explain, the tab shows a warning and the Explain button is disabled. Plans run earlier are still listed.
• Hypothetical Index needs a PostgreSQL component and at least one topology snapshot of it. On a database that DBGorilla has only just started watching, the tab appears after the first snapshot arrives.

Whoever runs the collector allows explain in the database’s commands list in collector.toml, as described in Database commands.

Find the queries that cost the most

DBGorilla groups statements under a fingerprint: the query shape with its literal values stripped out, so that

SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 99;

appear as one row. On PostgreSQL, the fingerprint is a short hex identifier such as b43e1f8279cc2861.

The list sorts by one dimension at a time. Eleven are available:

Dimension	Answers
total_duration	What is eating the most database time overall
avg_duration	What is slowest per call
invocations	What runs most often
rows	What moves the most rows
shared_blks_hit	What reads most from cache
shared_blks_read	What reads most from disk
shared_blks_written	What writes most
temp_blks_written	What spills to temporary files, which usually means work_mem
wal_bytes	What generates the most write-ahead log
block_read_time_ms	What spends the most time reading blocks
block_write_time_ms	What spends the most time writing blocks

Start with total_duration. A query that takes 8ms and runs two thousand times costs your database more than a query that takes two seconds and runs once. Once you have picked a target, avg_duration tells you whether it got faster.

The table shows the query text, the role that ran it, the dimension you ranked by, the call count, and the average execution time in milliseconds. Filter by role to separate your application’s traffic from the monitoring user’s. The filter is the button in the Role column header.

Search the list by a table or column name. The stored text contains the shape but no literals, so pasting in the statement you have in front of you often returns nothing. This can make the page appear broken.

Click a row to expand it. The expanded row shows the full statement, its numbers, its fingerprint, and the buttons Overview, Run Explain, Optimize Query and Hypothetical Index. Overview opens the query’s detail view with the aggregate numbers, a trend over time, and up to four tabs. These are Overview, Explain Plan, Optimizer and Hypothetical Index.

Read a query’s plan

EXPLAIN asks PostgreSQL what it would do with a query without running it. The plan shows whether PostgreSQL uses an index, where the row estimates are wrong, and which node is doing the work.

Explain runs against your live database, but it is plan-only and never executes the statement. The connection is real and goes to the member you selected. On the query’s Explain Plan tab, pick the member, then click Explain.

Running a new plan sends a live request through the collector. The collector must be online and must permit the explain command.

Past plans come from the audit log and remain readable with the collector offline. Explain history, on the Query Performance Explain Plan tab, lists every EXPLAIN run on the database, including when it ran, the server, and the query. That includes runs an assistant started from an editor.

Get a recommendation from the optimizer

There are two ways to reach the optimizer. Optimize is a tab on the Query Performance page, where you paste a query. Optimizer is a tab on a single query’s detail view, already pointed at that query.

Either way, it dispatches an agent run. The agent reads the plan, schema, and statistics, then returns a recommendation with its reasoning and evidence. That can be an index to add, or a finding that the query is already optimal.

Runs are asynchronous and have a history. After you dispatch one, it appears in the run table below the form with a live transcript. You can watch it work, leave the page, and return later. Past runs remain there.

The Optimize Query button on an expanded row of the Queries list opens that query’s Optimizer tab and starts a run.

Test an index before you build it

Use the Hypothetical Index tab before building an index. Paste a CREATE INDEX statement and click Simulate this index.

It answers “would this index help?” by putting the index in front of the planner on a clone of your database with your production statistics. The answer therefore reflects your real data distribution, and not an empty copy.

You get the planner’s cost before, the cost after, and whether the planner would use the index. Check the last value before building anything. Some sensible-looking candidates are indexes that the planner does not use.

Nothing is written to your live database, and no index is created there.

Use it from your editor

An assistant connected through Connect your editor reads the same data. get_top_workload ranks queries by any of the dimensions above, search_workloads finds them by text, and run_explain returns the plan for a member and database you name.

Related

• Empty pages: when the Queries list is empty.
• Database commands: allow or refuse explain.
• Experiments: the clone Hypothetical Index runs on.
• Connect your editor: use these tools from an AI assistant.