Analyzing Slow Queries with pg_stat_statements

When you’re working on real-world projects, your app might have thousands of users hitting it at the same time. They’re sending queries to your database, adding data, reading it, updating stuff... And then you notice your server starts to “groan.” That’s a sign your queries are far from optimal. Sometimes a query that looked “cute on paper” can turn into a performance disaster in practice. That’s where pg_stat_statements comes in.

pg_stat_statements lets you:

1. Track slow queries.
2. See how many times certain queries were run.
3. Find out how much time they took.
4. Check the average execution time for a query.
5. Avoid making the fatal mistake of rewriting your whole app!

Exploring the pg_stat_statements Structure

Once you activate the extension, your database gets a special view called pg_stat_statements. This is where all the data about executed queries lives. Let’s break down what’s inside:

SELECT * FROM pg_stat_statements LIMIT 1;

The result might look like this (simplified):

Quick explanations:

• query — the actual SQL query.
• calls — how many times this query was executed.
• total_time — the total time spent on this query.
• rows — number of rows returned by the query.
• shared_blks_read — number of blocks read (think: hitting the disk if you’re not using cache).

Analyzing the Results

Now that pg_stat_statements is enabled, let’s see how to find slow queries.

The Slowest Queries

To spot which queries are eating up the most time, you can use this query:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Here:

• mean_time — this is the average time for a single query (total_time / calls).
• ORDER BY total_time DESC — sorts by total execution time.

Frequently Executed Queries

Sometimes the problem isn’t slow queries, but queries that run way too often. For example:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Query Optimization

1. Use Indexing

If you see that queries on certain columns are slow, check if there’s an index for those columns. Say you have a students table with a ton of rows, and you often query the last_name field. You should create an index:

CREATE INDEX idx_students_last_name ON students (last_name);

2. Rewrite the Query

Let’s say you notice a query like SELECT * FROM orders WHERE amount > 1000 is taking forever. Most likely, instead of “everything about everything,” you should select only the columns you need:

SELECT order_id, amount FROM orders WHERE amount > 1000;

Clearing the Stats

Sometimes, to see only new results (like after you optimize something), you need to clear out the data in pg_stat_statements. Do it with this command:

SELECT pg_stat_statements_reset();

It works like the “Reset” button on your calculator. After you run it, stats will start collecting from scratch.

Finding Problem Queries

Imagine you’re the database admin for a university, and students are all complaining that their personal dashboard loads super slow. You decide to check pg_stat_statements:

Step 1: Find the slowest queries

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

You see a query like SELECT * FROM students WHERE status = 'active' takes 30 seconds. Yikes. Time to fix it.

Step 2: Check indexing After looking at the students table, you realize the status column doesn’t have an index. You fix that:

CREATE INDEX idx_students_status ON students (status);

Step 3: Check the result After optimizing, you check pg_stat_statements again and see the query now runs in 0.5 seconds. Victory!

Common Mistakes When Using pg_stat_statements

Sometimes admins make mistakes when analyzing queries:

1. Extension not activated. If you forgot to enable pg_stat_statements in shared_preload_libraries, stats just won’t be collected.
2. Ignoring indexing. Even if queries look slow, the problem might be solved by adding the right indexes.
3. Not resetting the stats. If you don’t run pg_stat_statements_reset(), old data gets in the way of analyzing what’s happening now.

Using pg_stat_statements in your workflow is like having a GPS for your database: it tells you exactly where you’re stuck in “traffic,” and even hints at how to get around it. Set up this tool right, and you’ll seriously boost your database performance.