A PostgreSQL database that slows down almost never has a single culprit. The slowdown comes from a handful of queries that run frequently, or from a few very expensive slow queries. Precisely identifying these queries is the prerequisite for any optimization.
PostgreSQL natively provides what you need to detect them. Third-party tools add convenience, but the foundation is in the server. This article describes the available data sources and how to combine them to build a reliable audit.
pg_stat_statements, the starting point
The pg_stat_statements extension ships with PostgreSQL and aggregates execution statistics per normalized query. It is enabled via shared_preload_libraries in postgresql.conf, then created in the database with CREATE EXTENSION pg_stat_statements.
Once in place, the pg_stat_statements view exposes, for each query fingerprint, the number of calls, the total time, the average time, the rows returned, and the blocks read from cache and from disk. A simple SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20 gives a clear view of what is loading the database.
This tool does not capture individual queries; it groups them by shape. Literals are replaced with parameters. That is precisely what makes it usable in production without saturating storage.
log_min_duration_statement for occasionally slow queries
pg_stat_statements gives an aggregated view but says nothing about a query that is only occasionally slow. To capture it, the log_min_duration_statement parameter logs every query whose duration exceeds a threshold.
Set to 500ms for example, it writes to the log every execution above that threshold, with the full SQL text and the duration. It is the go-to tool for spotting occasional spikes that do not stand out in the average.
The cost is moderate as long as the threshold is high. Setting this parameter to 0 logs every query and can fill up a disk quickly.
auto_explain to capture the plan
Knowing the duration of a slow query is not enough to fix it. You need its execution plan. The auto_explain extension automatically captures the EXPLAIN plan of every query exceeding a threshold, and writes it to the log along with the query.
Configured via auto_explain.log_min_duration and auto_explain.log_analyze, it gives the actual plan used in production, without manual intervention. It is the difference between knowing that a query is slow and understanding why.
In return, the log_analyze option re-runs in instrumented mode, which adds non-negligible overhead. Reserve it for targeted investigations.
EXPLAIN ANALYZE for manual investigations
For queries identified by pg_stat_statements or the logs, EXPLAIN ANALYZE remains the go-to tool. It actually runs the query and returns the effective plan with per-node timings, actual versus estimated rows, and loops.
The BUFFERS and FORMAT JSON options enrich the output. BUFFERS reveals disk reads and cache hits, which helps determine whether the slowness comes from disk access or from computation. FORMAT JSON makes it easier to send the plan to a visualizer such as explain.depesz.com or explain.dalibo.com.
GUI tools: pgBadger, pganalyze, QoreDB
pgBadger analyzes PostgreSQL logs offline and generates a detailed HTML report. It ranks queries by total time, by frequency, by user, and plots hourly activity charts. Suited to a post-mortem analysis or a regular audit.
pganalyze is a SaaS platform that continuously ingests pg_stat_statements and the logs, and offers index recommendations. Suited to teams that accept sending metrics to an external service.
QoreDB includes a Universal Query Interceptor that logs every query run from the client. The slow-query threshold is configurable, defaulting to 1000ms. Queries above it are retained with their fingerprint, their execution time, and their context. The P50, P95, and P99 percentiles are computed over the most recent executions and exposed in a dedicated panel. It is a client-side audit, complementary to the server tools.
A reproducible audit workflow
A regular audit goes through three steps. First, identify the expensive queries with pg_stat_statements for the aggregated view, and with log_min_duration_statement for the spikes. Next, retrieve the plan of each priority query with auto_explain or a manual EXPLAIN ANALYZE. Finally, act: adding an index, rewriting the query, or changing configuration.
This loop repeats. An optimization often shifts the bottleneck to another query, and only continuous measurement makes you aware of it.
Auditing slow queries is nothing exotic in PostgreSQL: the building blocks are native and mature. The work consists of combining them and sustaining them over time. A client tool like QoreDB rounds out this chain by offering a local, fingerprinted audit of what passes through the developer, and remains compatible with the classic server tooling.
Stay updated on new releases
Subscribe to get product releases, new drivers notifications, and technical tutorials.