Finding Slow Queries in Applications Using PostgreSQL Query Statistics

Database performance problems often appear as slow API responses, delayed background jobs, or applications that become unresponsive during periods of high traffic.

In many cases, the application code is not the main problem. A PostgreSQL query may be scanning too many rows, using an inefficient execution plan, waiting for locks, or repeatedly accessing the same data.

Finding these problems requires more than looking at the total database CPU usage. Developers need to understand which queries are consuming resources, how frequently they execute, and how their execution time changes over time.

PostgreSQL provides the pg_stat_statements extension to collect statistics about SQL statements executed by the database. It helps identify expensive queries, compare execution patterns, and decide which queries need further investigation.

This article explains how to enable query statistics, identify slow SQL statements, analyze execution plans, and use the collected information to improve database performance.

What Is pg_stat_statements?

pg_stat_statements is a PostgreSQL extension that tracks planning and execution statistics for SQL statements.

Instead of treating every query with different parameter values as a completely separate statement, it groups similar normalized queries.

For example, these queries have different customer IDs:

SELECT *
FROM customers
WHERE customer_id = 101;

SELECT *
FROM customers
WHERE customer_id = 205;

They can be grouped into a normalized query pattern:

SELECT *
FROM customers
WHERE customer_id = $1;

This makes it easier to understand the overall cost of a query pattern rather than investigating every individual execution separately.

The extension can provide information such as:

Number of executions.
Total execution time.
Average execution time.
Minimum and maximum execution time.
Rows returned.
Shared block hits and reads.
Temporary block activity.
Planning statistics when planning tracking is enabled.

These measurements help identify queries that consume significant database resources.

Enable pg_stat_statements

Before using the extension, PostgreSQL must be configured to load its shared library.

In the PostgreSQL configuration file, add or update:

shared_preload_libraries = 'pg_stat_statements'

If other libraries are already configured, preserve them instead of replacing the entire setting.

For example:

shared_preload_libraries = 'pg_stat_statements,other_library'

The PostgreSQL server must be restarted for changes to shared_preload_libraries to take effect.

Next, connect to the target database and run:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify that the extension is available:

SELECT *
FROM pg_extension
WHERE extname = 'pg_stat_statements';

You can also inspect the current configuration:

SHOW shared_preload_libraries;

On managed PostgreSQL services, the configuration process may differ. Check the provider's supported extension and parameter settings before making changes.

The database role executing these commands must have the required permissions.

Identify Queries With the Highest Total Execution Time

A common mistake is to investigate only queries with the highest average execution time.

However, a query that takes 20 milliseconds and executes 100,000 times may consume more database resources than a query that takes 2 seconds and executes only once.

Start by finding statements with the highest cumulative execution time:

SELECT
    query,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
    ROUND(max_exec_time::numeric, 2) AS max_time_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This query highlights statement patterns that consume the most total execution time.

For example:

Query pattern	Calls	Average time	Total time
Customer lookup	100,000	15 ms	1,500,000 ms
Monthly report	10	2,000 ms	20,000 ms
Order summary	5,000	25 ms	125,000 ms

The customer lookup has the highest cumulative execution time because it runs frequently.

It may be a better optimization target than the monthly report, even though the report takes longer per execution.

Find Queries With High Average Execution Time

To identify queries that are individually slow, sort by average execution time:

SELECT
    query,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
    ROUND(max_exec_time::numeric, 2) AS max_time_ms,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms
FROM pg_stat_statements
WHERE calls > 0
ORDER BY mean_exec_time DESC
LIMIT 20;

This helps identify expensive operations such as:

Complex joins.
Large aggregation queries.
Reporting queries.
Queries without suitable indexes.
Queries that process too many rows.

However, average execution time can hide occasional slow executions.

For example, a query might normally take 10 milliseconds but occasionally take several seconds because of lock contention or changing data volumes.

Use maximum execution time and other monitoring information to investigate these cases.

Find Queries That Read Too Much Data

PostgreSQL tracks shared block activity for statements.

A shared block hit indicates that the required data was found in shared buffers. A shared block read indicates that a block had to be read into shared buffers from the underlying storage system.

You can inspect this activity using:

SELECT
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_read,
    temp_blks_written,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

A query with many shared block reads may be accessing a large amount of data.

Temporary block activity can also indicate that operations such as sorting or hashing are using temporary storage.

These statistics do not automatically prove that a query is inefficient. A legitimate analytical query may need to read a large dataset.

Use them to identify candidates for further investigation.

Analyze a Slow Query With EXPLAIN ANALYZE

pg_stat_statements helps identify expensive query patterns, but it does not explain exactly why a query is slow.

For that, use PostgreSQL's EXPLAIN command.

Consider this query:

SELECT
    order_id,
    customer_id,
    total_amount,
    created_at
FROM orders
WHERE customer_id = 105
ORDER BY created_at DESC;

To inspect its execution plan, run:

EXPLAIN
SELECT
    order_id,
    customer_id,
    total_amount,
    created_at
FROM orders
WHERE customer_id = 105
ORDER BY created_at DESC;

The plan describes how PostgreSQL intends to execute the query.

For actual execution measurements, use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    order_id,
    customer_id,
    total_amount,
    created_at
FROM orders
WHERE customer_id = 105
ORDER BY created_at DESC;

ANALYZE executes the statement and reports actual execution measurements. BUFFERS provides information about buffer activity.

For a read-only SELECT, this is generally straightforward. Be careful with statements that modify data because EXPLAIN ANALYZE executes them.

Look for:

Sequential scans over large tables.
Unexpected nested loops.
Large differences between estimated and actual row counts.
Expensive sorting operations.
High buffer reads.
Repeated access to large datasets.

Do not assume that a sequential scan is always bad. PostgreSQL may correctly choose one when a large percentage of the table needs to be read.

The objective is to understand whether the execution plan matches the workload.

Optimize Queries With Appropriate Indexes

Suppose the orders table contains millions of records and the application frequently retrieves a customer's latest orders.

The query is:

SELECT
    order_id,
    customer_id,
    total_amount,
    created_at
FROM orders
WHERE customer_id = 105
ORDER BY created_at DESC;

A composite index may help:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

The index is designed around the filtering and sorting columns.

However, index creation should be based on the actual execution plan and workload.

Too many indexes increase storage usage and add maintenance overhead to INSERT, UPDATE, and DELETE operations.

For production tables with significant write traffic, consider the operational impact of index creation. PostgreSQL supports CREATE INDEX CONCURRENTLY in situations where reducing write-blocking is important, although it has its own restrictions and cannot run inside a transaction block.

Understand Query Statistics Correctly

Query statistics are useful, but they have limitations.

Statistics are cumulative

Many pg_stat_statements values accumulate over time since the statistics were last reset or the server was restarted.

A query may appear expensive because it has been running for several weeks.

For accurate comparisons, collect periodic snapshots and calculate differences between them.

Normalized queries can hide parameter differences

Queries with the same normalized structure may have very different execution characteristics depending on parameter values.

For example, one customer may have 10 orders while another has several million.

A single average can hide this difference.

Use representative parameter values and inspect actual execution plans when investigating parameter-sensitive workloads.

Statistics are not real-time query monitoring

The extension provides aggregated statement statistics. It is not a replacement for monitoring currently running queries or investigating lock waits.

For active queries, PostgreSQL also provides pg_stat_activity.

For example:

SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    query_start,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

This can help identify queries that are currently running or waiting.

Be mindful of permissions and sensitive information when accessing query text.

Build a Query Performance Monitoring Workflow

A useful production workflow combines query statistics, execution plans, and application-level monitoring.

Stage	Action	Purpose
Collection	Enable pg_stat_statements	Capture query statistics
Discovery	Find high-cost query patterns	Identify optimization targets
Investigation	Run EXPLAIN ANALYZE	Understand execution behavior
Optimization	Review indexes and SQL	Improve query efficiency
Validation	Compare before and after	Verify improvements
Monitoring	Track recurring patterns	Detect regressions

For a more reliable comparison, collect snapshots at regular intervals and compare the same workload periods.

Do not reset production statistics casually just to obtain a clean report. Export or preserve existing measurements first, and coordinate resets with the monitoring process.

Common Mistakes to Avoid

Optimizing only the slowest individual query

A frequently executed query can consume more resources overall than a rarely executed query. Consider both average and total execution time.

Adding indexes without examining execution plans

An index may not improve a query if PostgreSQL already has a better execution strategy. It may also increase write overhead.

Ignoring temporary block activity

Large sorts and hash operations can generate temporary I/O. Investigate these operations when temporary block usage is high.

Comparing cumulative statistics from different periods

Cumulative values are not directly comparable unless the collection intervals are known. Use periodic snapshots.

Running EXPLAIN ANALYZE on unsafe statements

EXPLAIN ANALYZE executes the statement. Be especially careful with data-modifying operations in production.

Ignoring lock waits

A query can appear slow because it is waiting for another transaction. Investigate pg_stat_activity and lock information before changing SQL or indexes.

Best Practices for Production PostgreSQL Monitoring

Enable pg_stat_statements where supported.
Track total execution time and average execution time.
Monitor query frequency alongside execution cost.
Review shared and temporary block activity.
Investigate slow queries with EXPLAIN (ANALYZE, BUFFERS).
Compare estimated and actual row counts.
Create indexes based on measured workload requirements.
Monitor active queries and lock waits separately.
Preserve periodic statistics snapshots.
Recheck query performance after application and schema changes.

Also review database configuration and workload changes when performance degrades. A query that performed well with a small dataset may need a different indexing strategy as the application grows.

Summary

PostgreSQL query statistics help developers identify SQL statements that consume significant execution time and database resources.

The pg_stat_statements extension provides aggregated information about query frequency, execution time, row counts, and buffer activity. Developers can use these measurements to identify expensive query patterns and prioritize optimization work.

However, query statistics only show where to investigate. Execution plans, buffer information, active query monitoring, and application metrics are needed to understand the underlying cause.

By combining pg_stat_statements, EXPLAIN ANALYZE, appropriate indexing, and periodic performance reviews, development teams can identify database bottlenecks and make optimization decisions based on actual workload data.