Optimize Queries with pg_stat_statements

You've been running the same PostgreSQL database for a while, and suddenly, queries that used to be snappy are crawling. You're not sure which query is the culprit — it could be a missing index, a badly written join, or a runaway aggregate. Worse, your application has hundreds of queries, and you can't manually inspect each one. This is the exact problem pg_stat_statements solves: it gives you a bird's-eye view of every query your database runs, along with its execution time, frequency, and resource usage. In this lesson, you'll learn how to enable this powerful extension, interpret its statistics, and use that data to find and fix your slowest queries — turning guesswork into a systematic optimization workflow.

The problem this lesson solves

When applications slow down, the database is often the bottleneck. But without visibility into which queries are consuming the most time, you're left debugging in the dark. You might add indexes randomly, rewrite queries blindly, or even restart the database in desperation. None of these approaches are reliable or scalable.

pg_stat_statements is PostgreSQL's built-in query profiler. It tracks every query that executes, recording how often it runs, how long it takes, and how much memory, disk, and CPU it uses. This data is aggregated per query, so you can instantly spot your top offenders: the queries that run the most, the ones that take the longest, and the ones that cause the most disk I/O.

Without this tool, you face: - Blind performance tuning — guessing which index to add without knowing which query is slow. - Wasted time — rewriting queries that aren't actually the bottleneck. - Production surprises — discovering a slow query only after it brings down the app.

By the end of this lesson, you'll be able to optimize queries with pg_stat_statements systematically, moving from guesswork to data-driven decisions.

Core concept / mental model

Think of pg_stat_statements as a flight recorder for your database. Just as an airplane logs every instrument reading during a flight, PostgreSQL logs every query execution. But instead of gigabytes of raw data, it aggregates the statistics by a normalized form of the query text.

How it works in simple terms: 1. The extension intercepts each query execution. 2. It normalizes the query by replacing literal values with placeholders. For example, SELECT * FROM users WHERE id = 42 becomes SELECT * FROM users WHERE id = $1. 3. It then stores cumulative counters: how many times that normalized query ran, total time spent, average time, rows returned, and more.

This normalization is critical — it means you don't get one row per individual query; you get one row per query pattern. This lets you see which pattern is used most often and which one is the slowest overall.

Key terms you'll encounter: - calls — How many times that query was executed. - total_exec_time — Total time spent executing that query (in milliseconds). - mean_exec_time — Average execution time per call. - rows — Total rows returned by all executions. - blk_read_time / blk_write_time — Time spent reading from or writing to disk.

Think of total_exec_time as the impact of a query — a query that runs 1,000 times at 10ms each (10 seconds total) is more impactful than a query that runs once at 100ms (0.1 seconds total). Your goal is to reduce the queries with the highest impact.

How it works step by step

Now let's look at the mechanics of actually using pg_stat_statements. Follow these steps in order.

Step 1: Enable the extension

First, ensure pg_stat_statements is installed. It's included in the standard PostgreSQL distribution but is not enabled by default. Add the following to your postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Then restart PostgreSQL. On most systems, you can do this with:

sudo systemctl restart postgresql

Pro tip: The shared_preload_libraries setting requires a restart. If you can't restart, you can still enable the extension, but it will only track queries from the moment you create it — which is fine for a quick analysis.

Step 2: Create the extension

Once PostgreSQL is restarted, create the extension in your database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

This creates the pg_stat_statements view (and a function) that you'll query.

Step 3: Let your application run

To get useful statistics, let your application run for a while — ideally during a typical workload. The extension accumulates data from the moment it's created, so even a few minutes of activity gives you initial data. The longer you wait, the more representative the data becomes.

Step 4: Query the statistics

You can now inspect the data. Start with the most impactful queries:

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

This shows the top 10 queries by total execution time. But you can also look at other metrics:

• By frequency: ORDER BY calls DESC to see which queries run most often.
• By average time: ORDER BY mean_exec_time DESC to see the slowest single executions.
• By rows affected: ORDER BY rows DESC to see queries that process huge datasets.

Step 5: Reset statistics when needed

To start fresh (e.g., after deploying a new version), use:

SELECT pg_stat_statements_reset();

This can be helpful for isolating the effect of a specific change.

Hands-on walkthrough

Let's put this into practice with a realistic scenario. Assume you have a simple users table and you want to find slow queries. We'll generate some traffic, then analyze the stats.

Set up a sample environment

First, create a small table and insert a few thousand rows:

-- Create a users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Insert 10,000 dummy rows
INSERT INTO users (name, email)
SELECT 
    'user' || g,
    'user' || g || '@example.com'
FROM generate_series(1, 10000) AS g;

Run some queries (simulate workload)

Run a query that searches by a non-indexed column — this will be slow:

-- A slow query: searches by name without an index
SELECT * FROM users WHERE name = 'user5000';

Also run a query that uses the primary key (fast):

-- Fast query: uses the primary key index
SELECT * FROM users WHERE id = 5000;

Repeat the slow query a few times to accumulate stats.

Analyze the statistics

Now query pg_stat_statements to see what happened:

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
WHERE query ILIKE '%users%'
ORDER BY total_exec_time DESC;

Expected output (abbreviated):

query                                                  | calls | total_exec_time | mean_exec_time | rows
--------------------------------------------------------+-------+-----------------+----------------+------
SELECT * FROM users WHERE name = $1                       |     3 |        12.345   |        4.115   |    1
SELECT * FROM users WHERE id = $1                         |     1 |         0.123   |        0.123   |    1
SELECT * FROM users INSERT INTO ... (the insert statement) |     1 |      300.123   |      300.123   |    0

You can see that the name query is much slower on average than the id query. The total_exec_time is high because we ran it multiple times, but even the mean is high. This tells you that the lack of an index on name is the problem.

Optimize the query

Now you can fix it by adding an index:

CREATE INDEX idx_users_name ON users (name);

Run the same query again and re-check the statistics:

-- Run the slow query again
SELECT * FROM users WHERE name = 'user5000';

-- Check stats again
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time
FROM pg_stat_statements
WHERE query LIKE 'SELECT * FROM users WHERE name = $1%'
ORDER BY mean_exec_time DESC;

Expected output:

query                              | calls | total_exec_time | mean_exec_time
----------------------------------- +-------+-----------------+----------------
SELECT * FROM users WHERE name = $1|     6 |         0.600   |        0.100

Notice how the mean_exec_time dropped from ~4ms to ~0.1ms — a 40x improvement. That's the power of pg_stat_statements guiding you to the right index.

Pro tip: Always run ANALYZE after adding an index so PostgreSQL updates its statistics and can use the index effectively.

Compare options / when to choose what

pg_stat_statements is not the only tool for query analysis. Here's how it compares to alternatives:

When to use what: - Start with pg_stat_statements to find the which and how often. - Then use EXPLAIN ANALYZE on the identified query to understand why. - Use auto_explain if you want to automatically capture plans for slow queries in production.

Variations: - pg_stat_statement_approximate views (available in some forks) provide normalized views for easier reporting. - Some cloud providers (RDS, Azure) automatically enable pg_stat_statements — check your instance settings. - If you need to track a specific session, you can use pg_stat_activity for real-time queries, but it doesn't aggregate history.

Troubleshooting & edge cases

Even with pg_stat_statements enabled, you might run into issues. Here are common problems and fixes.

The view is empty

• Symptom: No rows in pg_stat_statements after enabling.
• Cause: The extension was created but shared_preload_libraries wasn't set, so tracking didn't start. Restart PostgreSQL with the correct shared_preload_libraries and recreate the extension.
• Fix: Add the setting, restart, and CREATE EXTENSION again (or just restart — the extension is already there).

Only tracks after creation

• Symptom: Stats only include queries run after CREATE EXTENSION.
• Cause: That's by design; it only tracks queries from the moment it's initialized… depending on when it was loaded. If you want to capture startup queries, use shared_preload_libraries so it loads before any query runs.

Stats look odd — huge total time

• Symptom: A query shows extremely high total_exec_time.
• Cause: It might be a query that runs once but takes 30 seconds (like a maintenance job). Use mean_exec_time to see the average, and categorize by type (e.g., 'SELECT', 'INSERT') to filter.
• Fix: Use query filters like WHERE query LIKE '%SELECT%' or ORDER BY mean_exec_time DESC to find per-call slowpokes.

Permission denied

• Symptom: ERROR: permission denied for view pg_stat_statements
• Fix: Grant the pg_read_all_stats role to your user (or use a superuser).

GRANT pg_read_all_stats TO my_user;

Truncated query text

• Symptom: Queries appear cut off.
• Cause: The default pg_stat_statements.max (5000) can store many queries, but each query text is limited by track_activity_query_size (default 1024 bytes).
• Fix: Increase track_activity_query_size (e.g., to 4096) for longer queries, then restart and reset stats.

Stats don't reflect recent changes

• Symptom: After creating an index, stats still show old times.
• Cause: Stats are cumulative; you need to either reset them or wait for enough new executions to change the averages.
• Fix: Use pg_stat_statements_reset() to clear historical data and start fresh.

What you learned & what's next

Congratulations! You now know how to optimize queries with pg_stat_statements. Here's what you accomplished:

• You understood the core concept: pg_stat_statements aggregates query execution statistics per normalized query pattern.
• You enabled the extension and learned to query its key fields: calls, total_exec_time, mean_exec_time, and rows.
• You performed a hands-on exercise: identified a slow query, added an index, and verified the improvement.
• You compared pg_stat_statements with other tools like EXPLAIN ANALYZE and pgBadger, learning when to use each.
• You can now troubleshoot common issues like empty views, permission errors, and truncated query text.

Key takeaways: - pg_stat_statements is your first stop for identifying query bottlenecks. - Use total_exec_time (impact) not just mean_exec_time to prioritize queries. - Always follow up with EXPLAIN ANALYZE to understand the plan. - Reset stats after major changes to measure improvements accurately. - Enable shared_preload_libraries to ensure tracking starts at database boot.

What's next? Your next lesson will dive into understanding execution plans with EXPLAIN, where you'll learn to read the output of EXPLAIN ANALYZE in detail — the perfect next step after identifying a slow query. With pg_stat_statements in your toolkit, you'll be able to pinpoint the problem and then use EXPLAIN to fix it. Keep building your PostgreSQL mastery — you're on the right track!