PostgreSQL: Find Killer Queries with pg_stat_statements

When a critical application slows down, the database is often the first suspect. Investigating the cause of a PostgreSQL slowdown without the right tools is like finding a needle in a haystack, risking further compromise to system stability in production. The ability to quickly identify problematic queries is crucial for maintaining service performance and availability. PostgreSQL‘s pg_stat_statements extension offers a robust and detailed solution for monitoring and analyzing SQL query performance, allowing DBAs and developers to precisely pinpoint bottlenecks and implement targeted optimizations.

Tested on: PostgreSQL 16.3 · Ubuntu 24.04 LTS · July 2026

Prerequisites / Test Environment

To use pg_stat_statements, you need a functional PostgreSQL installation. The extension is available by default in most distributions but requires enablement. Ensure you have superuser permissions to modify the postgresql.conf configuration file and to create the extension within your databases. For testing, I used an Ubuntu 24.04 LTS virtual machine with PostgreSQL 16.3 installed from official repositories, simulating a workload with pgbench.

Enabling pg_stat_statements

Enabling pg_stat_statements requires two main steps: modifying the PostgreSQL configuration file and creating the extension within the desired databases.

First, locate your postgresql.conf file. Its location can vary depending on your distribution and installation method. On Debian/Ubuntu-based systems, it’s typically found at /etc/postgresql//main/postgresql.conf.

Open the file with a text editor and add pg_stat_statements to the shared_preload_libraries directive. If the directive is already present, add pg_stat_statements separated by a comma, as shown in the example:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

After modifying the file, you must restart the PostgreSQL service for the changes to take effect. Without a restart, the extension will not be loaded.

sudo systemctl restart postgresql

Once the service is restarted, connect to your database (or the default postgres database) and create the extension:

psql -U postgres -d your_database_name

CREATE EXTENSION pg_stat_statements;

This command makes the pg_stat_statements view available for querying. It’s advisable to create the extension in every database you wish to monitor, even though statistics are collected globally by the server.

Identifying the Query Killing Your Server

With pg_stat_statements enabled, you can start querying the view to identify problematic queries. The pg_stat_statements view contains several useful columns, including query (the query text), calls (the number of times the query was executed), total_exec_time (the total time spent executing the query in milliseconds), and mean_exec_time (the average execution time per call in milliseconds).

To find the queries that have consumed the most overall time, order by total_exec_time in descending order:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This will show you the top 10 most “expensive” queries in terms of resources. A query with a high total_exec_time might indicate an operation that takes a long time to complete or is executed very frequently. Read also: Elasticsearch OOM: Cluster RED from Unassigned Shard

If you want to identify queries that are slow in a single execution but perhaps not called very often, you can order by mean_exec_time:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

It’s also useful to filter by calls to see which queries are executed most frequently. Even a fast query, if called millions of times, can saturate the server:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

By analyzing these metrics, you can quickly narrow down the queries that are most significantly impacting your PostgreSQL server’s performance.

Query Analysis and Optimization

Once a problematic query is identified, the next step is to analyze it in detail and optimize it. The primary tool for this is EXPLAIN ANALYZE.

EXPLAIN ANALYZE executes the query and shows the actual execution plan, including execution times for each stage. This allows you to understand where the database is spending the most time, for example, due to sequential scans on large tables, inefficient joins, or a lack of appropriate indexes.

EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'value';

The output of EXPLAIN ANALYZE can be complex, but look for nodes like Seq Scan, Hash Join on very large tables, or high times in specific phases. The solution often lies in creating indexes (CREATE INDEX), rewriting the query for efficiency, or optimizing the database structure.

Common Errors and Troubleshooting

• pg_stat_statements not enabled: The most common issue is forgetting to restart the PostgreSQL service after modifying shared_preload_libraries or not executing CREATE EXTENSION. Check PostgreSQL logs (/var/log/postgresql/postgresql--main.log) for any errors during restart.
• Extension not available: If CREATE EXTENSION pg_stat_statements; fails, it might mean the extension is not installed. On Debian/Ubuntu, ensure the postgresql-contrib- package is installed.

    sudo apt install postgresql-contrib-16

• Outdated statistics: Statistics are updated in real-time, but if you’ve just enabled the extension or reset statistics, it might take some time for meaningful data to be collected. Ensure there is an active workload on the database.
• Truncated queries: The query column in pg_stat_statements has a configurable maximum length (pg_stat_statements.max_query_len). If your queries are very long, they might appear truncated. You can increase this value in postgresql.conf (requires a restart).

    # postgresql.conf
    pg_stat_statements.max_query_len = 2048 # Value in bytes

FAQ — Frequently Asked Questions

Does pg_stat_statements impact performance?

Yes, pg_stat_statements introduces a minimal overhead, as it needs to collect and store statistics for every executed query. However, for most production environments, the impact is negligible and largely justified by the diagnostic benefits. It’s an acceptable trade-off for the visibility it offers.

Do I need to restart the service after modification?

Absolutely. Adding pg_stat_statements to shared_preload_libraries in postgresql.conf requires a full restart of the PostgreSQL service to be loaded into shared memory. Without a restart, the extension will not be active and will not collect data.

Can I reset pg_stat_statements statistics?

Yes, you can reset all collected statistics by executing SELECT pg_stat_statements_reset();. This is useful after implementing optimizations or to start a new monitoring period, ensuring fresh data unaffected by past performance.

How do I interpret the query column output?

The query column shows the normalized query text, meaning parameter values are replaced with placeholders. This allows grouping statistics for similar queries, regardless of the specific values used. For example, SELECT FROM users WHERE id = $1 will group all queries like SELECT FROM users WHERE id = X.

Are there alternatives to pg_stat_statements?

Other tools exist for PostgreSQL monitoring, such as pg_top for real-time analysis of active processes and queries, or external monitoring solutions like Prometheus/Grafana with pg_exporter. However, pg_stat_statements remains the de facto standard for detailed analysis of individual SQL query performance due to its granularity and native integration.

Conclusions with Operational Takeaways

pg_stat_statements is an indispensable tool for any DBA or developer working with PostgreSQL. Its ability to provide detailed query performance statistics transforms an often complex investigation into a quick and targeted analysis. Enabling and using it regularly will allow you to keep your database performing, proactively identifying bottlenecks before they become critical issues. Remember to restart the service after enabling the extension and to use EXPLAIN ANALYZE to delve deeper into identified queries. Read also: Prometheus Alerting: Telegram, Email, PagerDuty with AlertManager

Sources

• PostgreSQL Documentation: pg_stat_statements
• PostgreSQL Documentation: EXPLAIN

Updated: July 2026