Query Insights
Query Insights lists the statements PostgreSQL actually ran slowly: how often each one ran, its mean and total time, its rows, and which instance's database it ran against.
Where to look
| Scope | Where |
|---|---|
| The whole server | Query Insights in the menu |
| One instance | The same page on the instance, and a card on its overview |
| One database | The Performance tab of that database |
Each view draws the range as a chart above the list, over the last hour, the last day, since the counters were reset, or a Custom range with a start and an end.
- Click a bar and the list shows the statements of that slot only.
- Click an item of the legend to hide or show that kind of bar.
- Chart Statements over the threshold and the list shows only the statements over the threshold too.
Values are never stored
Literal values are replaced by placeholders before anything is stored. A row shows the shape of a statement and never a customer's data.
This is worth being explicit about, because a slow-query log is otherwise one of the easiest ways to end up holding personal data you never meant to collect: the slowest queries on an Odoo system are frequently the ones carrying names, addresses and amounts.
It has to be collected first
Query statistics are loaded by the server install, and what is collected is on Configuration, on the PostgreSQL page's Query Insights tab.
| Setting | Default |
|---|---|
| The threshold above which a statement is recorded | 500 ms |
| Retention | Read at the sampler's next pass |
A PostgreSQL profile says whether its server is read at all, and can carry its own threshold - so a busy production cluster and a quiet one do not have to share a definition of slow.
Switching Collect statement statistics on or off recreates the managed PostgreSQL container once. Every instance loses its database connections for the seconds that takes - it is brief, but it is not nothing, so do it when a short interruption is acceptable.
From the command line
pelmo$ ohm server postgres slow-queries list --server pelmopelmo$ ohm server postgres slow-queries status --server pelmopelmo$ ohm server postgres slow-queries reset --server pelmoCheck now and Reset counters are queued jobs, like everything else that touches the server.
A statement that stays slow raises an alert
A statement that goes over the threshold raises the Slow query alert event
(database.slow-query) at warning, again while it stays slow, and once more, at the informational
severity, when it has stayed under the threshold for three sampling windows. It follows whatever
rule you have set rather than waiting to be noticed on a page nobody has open; without a rule on it,
it shows under the bell only.