Yuan Yao, An Oracle ACE's Blog

For general query performance troubleshooting, pg_stat_statements is often the most useful first extension to install. It tracks planning and execution statistics for SQL statements across a database server, groups similar statements, and exposes metrics such as call counts and execution time through the pg_stat_statements view. This helps identify queries that consume the most time or run most often.

Official documentation for this extension: The pg_stat_statements Module.

Installation and setup

The steps below show the captured commands in order, with an explanation of what each step does.

1. Check the initial error and configuration path

Connect to PostgreSQL and confirm the configuration file path. The initial query failed because the extension files had not yet been installed, the library had not been preloaded, and the extension had not been created in this database.

[opc@Yuan ~]$ psql -U postgres
psql (18.6)
Type "help" for help.
postgres=# select * from pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: select * from pg_stat_statements;
                      ^
postgres=# show config_file;
         config_file
-----------------------------
 /u01/pgdata/postgresql.conf
(1 row)
postgres=# \q

2. Install the PostgreSQL 18 contrib package

Install postgresql18-contrib, which provides the pg_stat_statements extension files for PostgreSQL 18.

[opc@Yuan ~]$ sudo dnf install -y postgresql18-contrib

3. Add the library to shared_preload_libraries

Open the PostgreSQL configuration file in vi:

[opc@Yuan ~]$ sudo vi /u01/pgdata/postgresql.conf

While editing the file, add pg_stat_statements to shared_preload_libraries. Preserve any libraries already listed. For example, add this configuration line:

shared_preload_libraries = 'pg_stat_statements'

4. Restart PostgreSQL

The library is loaded at server startup, so restart PostgreSQL after changing the configuration:

[opc@Yuan ~]$ sudo systemctl restart postgresql-18.service

5. Create the extension in the target database

The preload setting makes the library available to PostgreSQL. Create the SQL extension in each database where you want to query the view. This session created it in postgres:

[opc@Yuan ~]$ psql -U postgres -d postgres -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'
CREATE EXTENSION

Verify

Confirm that the extension is installed, review its current settings, and check that it records queries. The following commands and output were captured in psql:

postgres=# \dx pg_stat_statements;
                                                   List of installed extensions
        Name        | Version | Default version | Schema |                              Description
--------------------+---------+-----------------+--------+-----------------------------------
 pg_stat_statements | 1.12    | 1.12            | public | track planning and execution statistics of all SQL statements executed
(1 row)
postgres=# select name,setting from pg_settings where name like 'pg_stat_statements%';
               name                | setting
-----------------------------------+---------
 pg_stat_statements.max            | 5000
 pg_stat_statements.save           | on
 pg_stat_statements.track          | top
 pg_stat_statements.track_planning | off
 pg_stat_statements.track_utility  | on
(5 rows)
postgres=# SELECT extname FROM pg_extension WHERE extname = 'pg_stat_statements';
      extname
--------------------
 pg_stat_statements
(1 row)
postgres=# select left(query,50) as query from pg_stat_statements limit 1;
                       query
----------------------------------------------------
 SELECT extname FROM pg_extension WHERE extname = $
(1 row)

The output confirms extension version 1.12 is installed in the public schema and that the statistics view contains a captured query. The displayed settings show the current values for this server: pg_stat_statements.track = top tracks top-level statements, and planning statistics are disabled (track_planning = off).

Usage examples

Run these queries while connected to a database where the extension is installed. pg_stat_statements normalizes constants, so queries with different literal values are usually represented by one entry; query shows a representative form.

Find statements that consume the most total execution time

This helps prioritize work by cumulative database time. A query may rank highly because it is slow, runs often, or both.

SELECT calls,
       round(total_exec_time::numeric, 2) AS total_exec_ms,
       round(mean_exec_time::numeric, 2) AS mean_exec_ms,
       rows,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Find statements with a high average execution time

Filter out statements seen only a few times, then sort by average execution time. Adjust the 100 millisecond threshold for your workload.

SELECT calls,
       round(mean_exec_time::numeric, 2) AS mean_exec_ms,
       round(max_exec_time::numeric, 2) AS max_exec_ms,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls >= 10
ORDER BY mean_exec_time DESC
LIMIT 10;

Find the most frequently executed statements

This identifies high-frequency queries, including inexpensive statements that add up across many calls.

SELECT calls,
       round(mean_exec_time::numeric, 3) AS mean_exec_ms,
       rows,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Inspect shared buffer reads and hits

Compare blocks found in PostgreSQL shared buffers with blocks read into them. A high hit percentage alone does not prove a query is efficient.

SELECT calls,
       shared_blks_hit,
       shared_blks_read,
       round(100.0 * shared_blks_hit /
             nullif(shared_blks_hit + shared_blks_read, 0), 2) AS shared_buffer_hit_pct,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

Reset collected statistics

Reset counters only when you intentionally want to start a new observation period. Record any needed results first; this clears accumulated statistics. The function may require elevated privileges.

SELECT pg_stat_statements_reset();

Correlate active queries with their accumulated statistics

pg_stat_activity shows what each backend is doing now. pg_stat_statements shows accumulated statistics for normalized queries. Joining them can put a running query beside its historical call count and average execution time; those historical values do not measure the current execution’s duration.

SELECT a.pid,
       a.usename,
       a.application_name,
       now() - a.query_start AS running_for,
       a.wait_event_type,
       a.wait_event,
       s.calls,
       round(s.mean_exec_time::numeric, 2) AS mean_exec_ms,
       left(a.query, 120) AS current_query
FROM pg_stat_activity AS a
JOIN pg_stat_statements AS s
  ON s.dbid = a.datid
 AND s.userid = a.usesysid
 AND s.queryid = a.query_id
WHERE a.state = 'active'
  AND a.query_id IS NOT NULL
  AND a.pid <> pg_backend_pid()
ORDER BY a.query_start;

The join relies on query ID calculation being enabled (compute_query_id must not be off). Seeing activity from other users may require elevated monitoring privileges.