Using pg_stat_statements to Find and Analyze Problem Queries

calendar_today June 16, 2025 schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

Enable and configure the pg_stat_statements extension in PostgreSQL

Analyze query statistics to identify expensive or problematic queries

Use collected data to prioritize and guide query optimization efforts

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever wondered which queries are slowing down your database the most? How do you currently find and fix slow queries?

Lesson Overview

Duration: 90 minutes
Prerequisites: Basic PostgreSQL usage, ability to run SQL queries, some experience with query plans
Materials Needed: PostgreSQL instance (with superuser access), psql or GUI client, ability to modify configuration files or run ALTER SYSTEM

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

What is pg_stat_statements and why is it useful?
How to enable and configure the extension
Interpreting key columns and statistics
Hands-on practice: finding and analyzing slow queries
Common pitfalls and troubleshooting

Success Criteria:

By the end of this session, you should be able to use pg_stat_statements to find, analyze, and prioritize query optimization in your PostgreSQL database.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is pg_stat_statements?

pg_stat_statements is a PostgreSQL extension that tracks execution statistics for all SQL statements executed by the server. It helps you find the most expensive queries by time, frequency, or resource usage.

Real-World Analogy: Think of pg_stat_statements as a fitness tracker for your database—it records every “workout” (query) and helps you spot which ones are burning the most energy (resources).

Why This Matters: Without query-level statistics, it’s hard to know where to focus your optimization efforts. pg_stat_statements provides actionable data for tuning.

How It Works

Tracks normalized queries (with parameters replaced by placeholders)
Records execution count, total/average time, rows, and more
Data is stored in shared memory and can be queried like a regular table

Key Terminology

Extension: A module that adds extra features to PostgreSQL
Normalized Query: A query with literal values replaced by placeholders (e.g., SELECT * FROM users WHERE id = $1)
Total Time: Sum of execution times for all runs of a query
Mean Time: Average execution time per run
Calls: Number of times a query was executed

4. Detailed Explanation and Examples (25-30 minutes)

Enabling and Using pg_stat_statements

Step 1: Enable the Extension

-- As a superuser
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Step 2: Update postgresql.conf (if needed)

shared_preload_libraries = 'pg_stat_statements'

Requires a server restart if changed.

Step 3: Query the Statistics

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

Explanation:

This shows the top 10 queries by total execution time.
You can also sort by mean_time (average per call) or calls (frequency).

Resetting Statistics

SELECT pg_stat_statements_reset();

Useful to clear stats after a change or before a new measurement period.

Practical Applications

Identify slow or frequently run queries for optimization
Track the impact of code or index changes
Monitor query patterns over time

Common Misconceptions

Misconception: “pg_stat_statements slows down the database.” Reality: The overhead is minimal for most workloads and well worth the insight gained.
Misconception: “It shows the exact text of every query.” Reality: Queries are normalized, so similar queries are grouped together.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Finding the Most Expensive Queries

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

Explanation:

Shows which queries are using the most total time.
Useful for prioritizing optimization work.

Example 2: Analyzing Query Frequency

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

Key Points:

High-frequency queries may benefit from caching or indexing.
Even fast queries can add up if run thousands of times per second.

Example 3: Resetting and Monitoring Over Time

SELECT pg_stat_statements_reset();
-- Run your workload for a day
SELECT query, total_time, mean_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC;

Debugging Demonstration

Show a common mistake:

-- Forgetting to add pg_stat_statements to shared_preload_libraries
-- Extension appears to install, but no data is collected!

-- Corrected version
-- Add to postgresql.conf and restart the server
shared_preload_libraries = 'pg_stat_statements'

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Enable and use pg_stat_statements

Instructions:

Enable the extension and ensure it is loaded
Run several queries
Query pg_stat_statements to see statistics

Starter Code:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT * FROM pg_stat_statements LIMIT 5;

Solution:

-- You should see statistics for recent queries

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Find and analyze the slowest queries

Requirements:

Sort by total_time and mean_time
Identify queries with high mean_time but low call count
Suggest possible optimizations

Hints:

Look for queries with high mean_time (may be slow per run)
Look for queries with high total_time (may be run often)

Exercise 3: Creative Application (15 minutes)

Objective: Monitor the impact of an index or code change

Challenge:

Reset statistics
Apply an index or code change
Run your workload
Compare before/after statistics

Extension Ideas:

Automate reporting of top queries
Track statistics over time for trend analysis

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: Not restarting the server after changing shared_preload_libraries Cause: Extension is not loaded into memory Solution: Restart PostgreSQL after editing postgresql.conf
Problem: Misinterpreting normalized queries Cause: Placeholders hide literal values Solution: Use EXPLAIN on representative queries to analyze plans

Debugging Tips

Check if pg_stat_statements is loaded: \dx in psql
Use SHOW shared_preload_libraries; to confirm configuration
Reset stats before/after changes for clear measurement

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Always enable pg_stat_statements in production and staging
Regularly review top queries and optimize as needed
Document findings and share with your team

Next Steps in Your Learning Journey

Learn about other monitoring extensions (e.g., pg_stat_activity, auto_explain)
Explore query plan analysis in depth
Set up automated query performance alerts

9. Summary and Review (5 minutes)

Key Takeaways

pg_stat_statements is essential for query performance monitoring
Use it to find, analyze, and prioritize slow or expensive queries
Always test and measure the impact of optimizations

Quick Review Questions

What does pg_stat_statements track?
How do you enable the extension?
Why are normalized queries useful for analysis?

For Instructors

Teaching Tips

Demonstrate enabling the extension and show before/after stats
Encourage students to experiment with real workloads
Discuss the difference between total_time and mean_time

Assessment Ideas

Have students identify and optimize a slow query
Quiz on key columns and configuration steps
Peer review of optimization proposals

Adaptation Notes

For beginners: Focus on enabling and basic usage
For advanced: Explore trend analysis and automation
Online: Use cloud PostgreSQL or Docker for safe experimentation