# 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](/docs/troubleshooting/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](/docs/reference/collector-commands/#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 ```sql 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](/docs/integrations/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](/docs/troubleshooting/empty-pages/): when the Queries list is empty. - [Database commands](/docs/reference/collector-commands/#database-commands): allow or refuse `explain`. - [Experiments](/docs/using-dbgorilla/experiments/): the clone Hypothetical Index runs on. - [Connect your editor](/docs/integrations/editor/): use these tools from an AI assistant.