View statistics_query_activity
Query statistics about how often the query has been called and the roles and applications.
COUTION
The schema where the extension pg_stat_statements is installed has to be in the search_path of the user!
This view needs pg_stat_statements to be installed. When the extension is not installed, the view will not be installed by the extension.
COUTION
The view is only availabe in PostgreSQL >=14!
Columns
| Column Name | datatype | Description |
|---|---|---|
| database_name | name | The name of the database. |
| rolename | name | The role/user who executed the query. |
| backend_type | text | The type of current backend. Possible types are autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, archiver, standalone backend, startup, walreceiver, walsender, walwriter and walsummarizer. In addition, background workers registered by extensions may have additional types. |
| application_name | text | The name of the application that owns the backend, maybe empty when not set by the application. |
| query | text | This is the SQL source of the query. |
| calls_by_all_rolls | bigint | The overall count of executions of this query, the same query can be executed by different roles. |
Example
SELECT *
FROM statistics_query_activity;
| database_name | rolename | backend_type | application_name | query | calls_by_all_rolls |
|---|---|---|---|---|---|
| chinook | stefanie | client backend | DBeaver 25.1.5 - Metadata | select c.oid,pg_catalog.pg_total_relation_size(c.oid) as total_rel_size,pg_catalog.pg_relation_size(c.oid) as rel_size FROM pg_class c WHERE c.relnamespace=$1 | 1 |
| walwriter | |||||
| background writer | |||||
| checkpointer |