PostgreSQL Insights
Use Insights to find the statements that cost a PostgreSQL instance the most over a day or a week, and the statements that changed.
Before you begin
- You need the View PostgreSQL and View Insights permissions.
- The lists use
pg_stat_statements. If Scops cannot read it, one sentence in place of the lists tells you why. See PostgreSQL permissions. - Scops counts the statements every ten minutes, and keeps them for 15 days by default (see Retention).
- To show one database, select it in the database bar. All databases shows the full instance.
Choose the time window
| Button | Window |
|---|---|
| 1D | The last 24 hours, in hourly bars. This is the default. |
| 1W | The last 168 whole hours, in 6-hour bars. |
| Calendar button | Any range of whole hours, up to 7 days. Select the start and the end, then click Apply. |
To read the newest hours again, click the button of the window that shows. All times use the clock of the instance.
Read the Activity chart
Each bar of the chart stacks the average active backends of its period. Select how to split it: Database (the default), User or Application. When you show one database, the choices are User (the default) and Application.
The list next to the chart shows the 20 largest values of the window with their average. Other holds all the rest.
Narrow the lists
To show the statements of one value, click its row in the list next to the chart. The other bands fade.
To show the statements of one period, click a bar of the chart.
To go back, click the chip in the chart header, or click the same row or bar again.
By application, the lists show the statements that the samples saw for that application. Their numbers include all callers, because pg_stat_statements does not count by application.
Find the heaviest statements
The Heaviest Statements tab lists the statements that cost the instance the most in the window, 100 at most. Select the measure to rank by.
| Rank by | Finds |
|---|---|
| Total Time | Where the instance spent its time. This is the default. |
| Mean Time | What is slow each time it runs. |
| Calls | What runs too often, for example a loop in the application. |
| Blocks Read | The blocks read from outside shared_buffers. |
| Temp Blocks Written | Sorts and hashes that did not fit in work_mem. |
The ranked column shows the value and its share of the window. The tag nested means that the statement ran inside a function or a procedure. Its numbers are also in the numbers of its callers.
Find the new statements
The New Statements tab lists the statements that Scops first counted in the window, for example after a release. It shows First Seen, Calls, Mean Time, Shared Blocks and Total Time.
The list needs one day of counts. Before that, it tells you from when it can find new statements.
Find the statements that run slower
The Slower Than Usual tab lists the statements whose mean time is 1.5 times their usual or more. The usual is their mean time over the week before the window.
| Column | What it shows |
|---|---|
| Mean Time, Usual | The mean time in the window and in the week before it. |
| Slower By | How many times slower, for example 2.4×. |
| Extra Time | What the slowdown cost in the window: the extra time of one call, times the calls. The list is in this order. |
A statement needs 10 calls or more in the window and in the week before. Its extra time must be at least 0.1% of the time of all statements.
Open a statement
Click a row. The statement details open.
Read the line under the lists
The line under the lists shows how many statements were counted, and the period that the counts cover. It also tells you when:
pg_stat_statementsremoved entries because it was full. Then some statements can be missing.- The counts were reset in the window.
Related
pg_stat_statements needs.
Updated November 22, 2026