How to Find Slow PostgreSQL Queries with pg_stat_statements

How to Find Slow PostgreSQL Queries with pg_stat_statements#

An API starts getting slower. Database CPU rises. A dashboard occasionally times out.

You know PostgreSQL is involved, but you do not yet know which SQL statement deserves attention.

This is where query optimization often starts badly. It is tempting to inspect the largest table, add an index to a suspicious column, or optimize the query that looks most complicated in the codebase.

None of those approaches answers the first question:

Which queries are actually consuming database time?

pg_stat_statements helps answer that question by aggregating planning and execution statistics for SQL statements executed by PostgreSQL. Instead of looking at one request at a time, it lets us inspect the workload as a whole: how often each query runs, how much execution time it accumulates, how expensive the average execution is, and how much database work it performs.

The goal is not to find the query with the ugliest SQL.

It is to find the query patterns that deserve investigation.

pg_stat_statements Is Workload History, Not a Live Activity View#

Before using it, there is an important distinction.

pg_stat_statements answers questions such as:

• Which query patterns have consumed the most execution time?
• Which statements run most frequently?
• Which queries have high average latency?
• Which statements generate substantial block or temporary-file activity?

It does not primarily answer:

What query is running or waiting right now?

For current sessions and currently executing statements, pg_stat_activity is usually the more appropriate starting point.

Think of the distinction this way:

pg_stat_activity
    ↓
What is happening now?

pg_stat_statements
    ↓
What has accumulated cost over time?

If an incident is happening at this moment, both can be useful.

If you are trying to understand which query patterns have been expensive across a representative workload, pg_stat_statements is the better tool.

What pg_stat_statements Actually Measures#

pg_stat_statements tracks statistics for SQL statements executed by the PostgreSQL server.

Useful columns include:

• calls: number of executions;
• total_exec_time: total execution time accumulated by the statement;
• mean_exec_time: average execution time;
• min_exec_time and max_exec_time;
• stddev_exec_time: variation in execution time;
• rows: total rows retrieved or affected;
• shared_blks_hit and shared_blks_read;
• temporary block reads and writes;
• WAL-related statistics;
• planning statistics when planning tracking is enabled.

Execution-time fields are measured in milliseconds. stddev_exec_time is a standard deviation, not a percentile such as p95.

Execution statistics are recorded after successful execution. Failed or canceled statements are not a complete part of this history; inspect logs and pg_stat_activity too when investigating timeouts.

The statistics are grouped by dimensions including database, user, query identifier, and whether the statement was top-level.

PostgreSQL also normalizes query structures for statistics collection.

Suppose an application executes:

SELECT id, total
FROM orders
WHERE account_id = 42;

and later:

SELECT id, total
FROM orders
WHERE account_id = 917;

The tracked query can be represented with the literal replaced by a parameter:

SELECT id, total
FROM orders
WHERE account_id = $1;

That is usually what we want.

The interesting unit is not one individual request with one specific account ID. It is the SQL pattern repeatedly generated by the application.

How to Enable pg_stat_statements#

pg_stat_statements is a supplied PostgreSQL module, but collecting its statistics requires server-level configuration.

The module must be loaded through shared_preload_libraries because it requires shared memory:

shared_preload_libraries = 'pg_stat_statements'

Add pg_stat_statements to any existing library list rather than replacing its other entries. Changing shared_preload_libraries requires a PostgreSQL restart.

PostgreSQL also needs query identifiers. The default compute_query_id = auto configuration allows modules such as pg_stat_statements to enable query identifier calculation when required.

After restarting PostgreSQL, create the extension in the database where you want to access its views:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

You can verify that it exists with:

SELECT extname
FROM pg_extension
WHERE extname = 'pg_stat_statements';

The statistics collector itself operates at the PostgreSQL server level, while the extension exposes the SQL views and functions in databases where it has been installed.

On managed PostgreSQL services, you may not edit postgresql.conf directly. The provider may expose shared_preload_libraries through a parameter group, configuration panel, or another platform-specific mechanism.

The PostgreSQL requirement remains the same; only the operational procedure changes.

Understand What pg_stat_statements.track Includes#

Another configuration detail affects what appears in the statistics.

By default:

pg_stat_statements.track = top

This means PostgreSQL tracks top-level statements issued directly by clients.

SQL executed inside functions is not normally tracked as a separate nested statement under this configuration.

If you need nested statements to be tracked independently, the setting can be changed to:

pg_stat_statements.track = all

That provides more granular visibility, but it also changes the amount of data collected.

Utility commands are controlled separately through:

pg_stat_statements.track_utility

which is enabled by default.

Do not change these settings simply to maximize the number of rows in the view. The useful configuration is the one that reflects the workload you are actually trying to diagnose.

Start with Total Execution Time#

Once statistics have accumulated during a representative workload, a useful first query is:

SELECT
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

The dbid filter matters because pg_stat_statements can contain statistics for multiple databases on the same PostgreSQL server.

This query asks a better question than:

Which statement had the slowest individual execution?

It asks:

Which query patterns consumed the most execution time overall?

That distinction matters.

A query can have a high mean_exec_time but run only occasionally.

Another query may take only a small amount of time per execution but run continuously. Its cumulative cost can be much larger.

If you sort only by average latency, you can end up optimizing an obvious slow query while ignoring the statement that consumes more total database capacity.

total_exec_time, mean_exec_time and calls Answer Different Questions#

These three columns should be interpreted together.

total_exec_time: where is database execution time going?#

Start with:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

A statement near the top is not necessarily badly written.

It may simply perform important work very frequently.

That is still valuable information.

Optimization is partly an economic decision. If reducing the cost of one SQL pattern removes a meaningful amount of total database work, the query can be worth investigating even when a single execution does not look slow.

mean_exec_time: which executions are individually expensive?#

Now change the ordering:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY mean_exec_time DESC
LIMIT 20;

This surfaces statements with expensive average executions.

They may be latency candidates, but context matters.

A reporting query executed once per day can legitimately have a much higher average execution time than an API lookup executed thousands of times per minute.

The useful question is not simply:

Is the average high?

It is:

Is this execution time inappropriate for what this query does and how the application depends on it?

calls: which query patterns dominate by frequency?#

A third view is:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY calls DESC
LIMIT 20;

A high-frequency statement is not automatically a performance problem.

But frequency changes the economics of an optimization.

Removing a small amount of work from a query executed continuously can matter more than dramatically improving a statement that almost never runs.

That is why calls, mean_exec_time, and total_exec_time should not be treated as competing rankings.

They describe different dimensions of the workload.

Look for Unstable Queries, Not Only Slow Averages#

Averages can hide useful information.

pg_stat_statements also exposes:

min_exec_time
max_exec_time
stddev_exec_time

For example:

SELECT
    query,
    calls,
    mean_exec_time,
    min_exec_time,
    max_exec_time,
    stddev_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY stddev_exec_time DESC
LIMIT 20;

A query with a moderate average but a wide execution-time distribution may deserve investigation.

That still does not tell us why execution time varies.

Possible causes include different parameter selectivity, cache state, lock contention, temporary I/O, concurrent workload, or changing execution plans.

Those are hypotheses.

pg_stat_statements identifies the pattern. It does not establish the root cause.

Use Block Statistics to Estimate How Much Data Work a Query Generates#

Execution time tells us where time accumulated.

Block statistics add another perspective.

SELECT
    query,
    calls,
    total_exec_time,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

shared_blks_hit counts shared-buffer hits.

shared_blks_read counts shared blocks that PostgreSQL had to read rather than satisfy as shared-buffer hits.

Do not interpret shared_blks_read as a direct count of physical disk operations.

PostgreSQL may request a block that is not currently in shared buffers while the operating system still satisfies the underlying read from its own filesystem cache.

The important distinction is:

shared_blks_hit
→ PostgreSQL found the page in shared buffers

shared_blks_read
→ PostgreSQL had to perform a read for the page

Neither metric alone proves that a query is good or bad.

A statement can generate millions of shared-buffer hits and still consume significant CPU while processing those pages.

Likewise, a high number of reads does not prove that adding an index is the correct fix.

These counters tell us about workload.

The execution plan later tells us how PostgreSQL produced that workload.

Temporary Blocks Can Reveal Additional Work#

The fields:

temp_blks_read
temp_blks_written

can be useful when operations require temporary files.

Large sorts, hashes, or other operations that cannot remain within the available working memory may generate temporary I/O.

Again, the presence of temporary blocks is evidence, not a complete diagnosis.

It tells us that temporary storage was involved.

To determine which plan node caused it and why, we still need the execution plan.

I/O Timing Requires track_io_timing#

pg_stat_statements can also expose time spent reading and writing data blocks.

However, those timing fields depend on:

track_io_timing = on

When I/O timing is disabled, no new I/O time is collected; previously accumulated values may remain.

Collecting I/O timing can add some overhead depending on the platform's timing implementation, so it should be enabled deliberately rather than assumed to be free in every environment.

Check the Measurement Window Before Trusting the Ranking#

Cumulative statistics without a time window are easy to misinterpret.

Before acting on the top statements, inspect:

SELECT *
FROM pg_stat_statements_info;

One useful field is:

stats_reset

which indicates when the module-wide statistics were last reset.

The view also exposes:

dealloc

which tells us how many times statistics for less-executed statements had to be discarded because the module exceeded its configured statement capacity.

PostgreSQL 17 and later also expose:

stats_since

for individual pg_stat_statements entries.

That matters because total_exec_time is cumulative.

A query that has accumulated statistics for days should not be casually compared with a statement that appeared five minutes ago without considering the measurement window.

For recurring analysis, snapshots taken over defined periods are often easier to reason about than repeatedly resetting the statistics.

PostgreSQL does provide:

SELECT pg_stat_statements_reset();

but resetting the statistics destroys accumulated information.

Use it deliberately.

Watch for Statement Eviction#

pg_stat_statements cannot retain an unlimited number of distinct entries.

The setting:

pg_stat_statements.max

controls how many statements can be tracked.

If PostgreSQL observes more distinct statements than the configured capacity allows, statistics for less-executed statements can be discarded.

Check:

SELECT
    dealloc,
    stats_reset
FROM pg_stat_statements_info;

If dealloc keeps increasing during the period you are trying to analyze, some lower-frequency statement statistics are being removed.

That does not automatically mean you should increase pg_stat_statements.max.

A larger capacity also requires more shared memory.

The useful question is whether the configured capacity preserves enough of the workload for the analysis you need.

pg_stat_statements Does Not Tell You Why a Query Is Slow#

This is the boundary that makes the tool useful.

It can tell us:

this query runs frequently

this query consumes substantial total execution time

this query has a high average execution time

this query generates large block activity

this query has unstable execution times

It cannot tell us, by itself:

this index is missing

this sequential scan is wrong

the planner underestimated these rows

this join order caused the problem

this sort spilled because work_mem was insufficient

this predicate is not selective enough

Those are execution-plan questions.

Once a candidate query has been identified, the workflow changes:

pg_stat_statements
        ↓
identify an expensive query pattern
        ↓
EXPLAIN (ANALYZE, BUFFERS)
        ↓
understand the work PostgreSQL performs
        ↓
change SQL, indexes, statistics or architecture
        ↓
measure again

For that next stage, continue with the PostgreSQL Query Optimization practical guide, which covers execution plans, row estimates, indexes, planner statistics and verification.

pg_stat_statements should narrow the search space.

It should not replace execution-plan analysis.

Do Not Enable Planning Statistics Without a Reason#

pg_stat_statements can collect planning-time statistics as well as execution statistics.

Planning statistics are controlled by:

pg_stat_statements.track_planning

They are disabled by default.

Enabling planning tracking can introduce additional overhead, particularly under workloads with many concurrent sessions repeatedly updating planning statistics for equivalent query structures.

Do not enable it simply because more metrics sound better.

First determine whether planning time is part of the performance problem you are investigating.

For many initial slow-query investigations, execution statistics are already enough to decide which SQL patterns deserve deeper analysis.

Permissions Matter#

Production query statistics are operational data.

Access should be deliberate.

Superusers and roles with pg_read_all_stats can see query text and query identifiers belonging to statements executed by other users.

Less-privileged users do not automatically receive the same visibility into other users' query text.

That is worth considering before exposing pg_stat_statements through internal dashboards or granting broad database access purely for troubleshooting convenience.

A Practical Workflow for Finding Slow PostgreSQL Queries#

When the database appears slow but the responsible SQL is unclear, use a repeatable process.

1. Collect a representative workload#

Do not optimize immediately after enabling pg_stat_statements.

Let it observe enough activity to represent the workload you actually care about.

That may mean a busy production period, a controlled load test, or another clearly defined measurement window.

2. Rank statements by total execution time#

Start with:

SELECT
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

This identifies query patterns consuming the largest amount of accumulated execution time.

3. Compare frequency with average latency#

Look at:

calls
mean_exec_time
total_exec_time

Separate:

expensive because each execution is slow

from:

expensive because it executes constantly

Those are different optimization problems.

4. Look for variability#

Check:

min_exec_time
max_exec_time
stddev_exec_time

A stable query and an unpredictable query may require different investigations even when their average execution times are similar.

5. Inspect block and temporary-file activity#

Use:

shared_blks_hit
shared_blks_read
temp_blks_read
temp_blks_written

to understand how much database work the query pattern generates.

Do not treat those counters as the final diagnosis.

6. Verify the measurement window#

Check:

stats_reset
stats_since
dealloc

where supported by your PostgreSQL version.

Make sure you know what period the statistics represent and whether statement entries have been evicted.

7. Pick a candidate before changing anything#

At this point you should be able to make a concrete statement:

This query pattern deserves investigation because it accounts for substantial execution time and runs frequently.

That is a much stronger basis for optimization than:

This SQL looks inefficient.

8. Move to the execution plan#

Now inspect a representative execution with EXPLAIN, or with EXPLAIN (ANALYZE, BUFFERS) when executing the statement is safe.

The question changes from:

Which query should I investigate?

to:

Why does PostgreSQL perform this amount of work to execute it?

That is where query optimization begins.

Conclusion#

Finding slow PostgreSQL queries is not the same as sorting SQL statements by average execution time.

The useful target is the workload that costs the database enough to matter.

pg_stat_statements gives us several ways to observe that cost: frequency, cumulative execution time, average latency, variability, row counts and block activity.

None of those metrics should be interpreted alone.

Start with total_exec_time. Compare it with calls and mean_exec_time. Check the measurement window. Use variability and block activity when they help describe the workload.

Then stop.

Do not add an index because a statement appears at the top of the list. Do not rewrite it simply because its average execution time is high.

pg_stat_statements has identified where to investigate.

It has not yet explained the cause.

The reliable sequence is:

measure the workload
        ↓
identify the expensive SQL pattern
        ↓
inspect its execution plan
        ↓
understand the work
        ↓
change one thing
        ↓
measure again

The most useful query to optimize is not always the one with the slowest individual execution.

It is the one whose cost matters enough to investigate.

Reference#

PostgreSQL 17 documentation: pg_stat_statements. Check the documentation for your server version when comparing available columns.