Identify PostgreSQL® slow queries¶
PostgreSQL® allows you to keep track of queries with certain performance metrics and statistics, which comes in handy when identifying slow queries.
When using Aiven for PostgreSQL®, you can check the Query Statistics tab for your service in the Aiven Console to identify long running queries.
Under the hood, the Query Statistics tab uses the
pg_stat_statements extension, a module that provides a means for tracking the planning and execution statistics of all SQL statements executed by your PostgreSQL® server, to provide you with the basic information that can be useful for identifying slow queries.
These are the entries provided by the Query Statistics which are deduced via the
Text of a representative statement
Total number of rows retrieved or affected by the statement
Number of times the statement was executed
Minimum time spent executing the statement
Maximum time spent executing the statement
Mean time spent executing the statement
Population standard deviation of time spent executing the statement
Total time spent executing the statement
You can also create custom queries using the
pg_stat_statements view and use all the available columns to investigate your use case.
To query the
pg_stat_statements view, you’ll need to create the
pg_stat_statements extension (included in the list of available extensions) that can be done via the following
CREATE EXTENSION command:
CREATE EXTENSION pg_stat_statements;
Discover slow queries¶
You can run the following command to display the
pg_stat_statements view and all the columns contained:
With the result being for PostgreSQL 13:
View "public.pg_stat_statements" Column | Type | Collation | Nullable | Default ---------------------+------------------+-----------+----------+--------- userid | oid | | | dbid | oid | | | toplevel | boolean | | | queryid | bigint | | | query | text | | | plans | bigint | | | total_plan_time | double precision | | | min_plan_time | double precision | | | max_plan_time | double precision | | | mean_plan_time | double precision | | | stddev_plan_time | double precision | | | calls | bigint | | | total_exec_time | double precision | | | min_exec_time | double precision | | | max_exec_time | double precision | | | mean_exec_time | double precision | | | stddev_exec_time | double precision | | | rows | bigint | | | shared_blks_hit | bigint | | | shared_blks_read | bigint | | | shared_blks_dirtied | bigint | | | shared_blks_written | bigint | | | local_blks_hit | bigint | | | local_blks_read | bigint | | | local_blks_dirtied | bigint | | | local_blks_written | bigint | | | temp_blks_read | bigint | | | temp_blks_written | bigint | | | blk_read_time | double precision | | | blk_write_time | double precision | | | wal_records | bigint | | | wal_fpi | bigint | | | wal_bytes | numeric | | |
On older PostgreSQL versions you might find different column names (e.g. the column previously named
max_time is now
max_exec_time). Always refer to the PostgreSQL® official documentation with the version you are using for accurate column matching.
You can write custom queries to
pg_stat_statements to help you analyze recently run queries in your database.
Sort database queries based on
The following query, inspired by a GitHub repository, uses the
pg_stat_statements view, shows the running queries sorted descending by
total_exec_time, re-formats the
calls column and deduces the
SELECT interval '1 millisecond' * total_exec_time AS total_exec_time, to_char((total_exec_time/sum(total_exec_time) OVER()) * 100, 'FM90D0') || '%' AS prop_exec_time, to_char(calls, 'FM999G999G999G990') AS calls, interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time, query AS query FROM pg_stat_statements WHERE userid = ( SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1 ) ORDER BY total_exec_time DESC LIMIT 10;
You can run the above commands on your own PostgreSQL® to gather more information about how the recent queries are performing.
It is possible to discard the
pg_stat_statements previously gathered statistics by using the following command:
Find top queries with high I/O activity¶
The following SQL shows queries with their
id and mean time in seconds. The result set is ordered based on the sum of
blk_write_time meaning that queries with the highest read/write are shown at the top.
SELECT userid::regrole, dbid, query, queryid, mean_time/1000 as mean_time_seconds FROM pg_stat_statements ORDER by (blk_read_time+blk_write_time) DESC LIMIT 10;
See top time-consuming queries¶
Aside from the relevant information to the database, the following SQL retrieves the number of calls, consumption time in milliseconds as
total_time_seconds, and the minimum, maximum, and mean times such query has ever been executed in milliseconds. The result set is ordered in descending order by
mean_time showing the queries with most consumption time first.
SELECT userid::regrole, dbid, query, calls, total_time/1000 as total_time_seconds, min_time/1000 as min_time_seconds, max_time/1000 as max_time_seconds, mean_time/1000 as mean_time_seconds FROM pg_stat_statements ORDER by mean_time desc LIMIT 10;
Check queries with high memory usage¶
The following SQL retrieves the query, its
id, and relevant information about the database. The result set in this case is ordered by showing the queries with the highest memory usage at the top, summing the number of shared memory blocks returned from the cache (
the number of shared memory blocks marked as “dirty” during a request needed to be written to disk (
SELECT userid::regrole, dbid, queryid, query FROM pg_stat_statements ORDER by (shared_blks_hit+shared_blks_dirtied) DESC limit 10;