Using pg_stat_statements for PostgreSQL Query Performance & Health Checks

Introduction

pg_stat_statements is one of the most powerful PostgreSQL extensions...

Why You Should Use pg_stat_statements

PostgreSQL provides powerful introspection tools, and among the most critical for performance tuning is pg_stat_statements.

This extension captures historical query statistics, including:

• Normalized query text (so similar queries are grouped)
• Total execution count
• Execution time metrics
• Rows returned
• Block I/O and more

With pg_stat_statements, you gain a clear view into which queries consume the most resources, which are most frequently run, and which need tuning.

---

Installation Guide (Ubuntu 24.04 + PostgreSQL 17)

To enable pg_stat_statements on a modern Ubuntu 24.04 server running PostgreSQL 17, follow these steps:

1. Install contrib modules

sudo apt update
sudo apt install postgresql-contrib

This installs useful modules including pg_stat_statements.

---

2. Enable the Extension in PostgreSQL Config

Edit your postgresql.conf, usually at /etc/postgresql/17/main/postgresql.conf, and ensure:

session_preload_libraries = 'pg_stat_statements'

Then restart the service:

sudo systemctl restart postgresql

---

3. Create the Extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify:

SELECT * FROM pg_stat_statements LIMIT 5;

---

Sample Queries

🔍 Top Queries by Average Time

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

⏱️ Total Execution Time Consumers

SELECT
  query,
  total_exec_time,
  (total_exec_time / SUM(total_exec_time) OVER()) * 100 AS cpu_percentage
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

📊 Most Frequently Called Queries

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

Comprehensive pg_stat_statements Analysis Query (PostgreSQL 17)

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  round((100 * total_exec_time / SUM(total_exec_time) OVER())::numeric, 2) AS cpu_percentage
FROM
  pg_stat_statements
WHERE
  query NOT ILIKE '%pg_stat_statements%'
ORDER BY
  total_exec_time DESC
LIMIT 20;

What Does This Query Do? This query leverages the pg_stat_statements extension in PostgreSQL 17 to analyze the performance of SQL queries executed in the database. It helps answer questions like:

🔁 Which queries are executed most frequently? → via the calls column

🐢 Which queries consume the most total execution time? → total_exec_time

🕒 Which queries are the slowest on average? → mean_exec_time

📊 What percentage of the total CPU time does each query consume? → cpu_percentage

📈 How many rows does each query return on average? → rows

With this query, you can identify expensive queries (in terms of resource consumption) and also frequently executed queries that might benefit from optimization.

Notes: cpu_percentage: Represents the percentage of total query execution time that this particular query accounts for. A high value indicates a heavy query.

NOT ILIKE '%pg_stat_statements%': Filters out introspective queries related to the statistics view itself.

LIMIT 20: Limits the output to the top 20 queries based on total execution time to keep the results readable.

---

Resetting Statistics

SELECT pg_stat_statements_reset();

This reset is lightweight and safe to use in production.

---

pg_stat_statements Evolves Over Time

Each PostgreSQL version adds new metrics to pg_stat_statements. For example, jit_*, plans, and other fields have been added recently.

You can follow its evolution at:

• 🔵 pgPedia Overview

You can find detailed info about pg_stat_statements on this page :

• 🔵 PostgreSQL Docs

---

How pghealth Uses pg_stat_statements

At pghealth, we use pg_stat_statements to help you:

• 🔍 See slow queries visually
• ⚠️ Detect performance bottlenecks
• 📊 Monitor query metrics without writing SQL

---

Final Thoughts

pg_stat_statements is essential for:

• Real-time query analysis
• Performance tuning
• Historical insight

Combined with pghealth, you can detect and fix query issues in seconds—before they become problems.

Questions?

Email us at admin@pghealth.io