pg_stat_statements for slow queries in PostgreSQL

Introduction

pg_stat_statements helps you find slow queries in your PostgreSQL database. It shows which queries take the most time, so you can fix them and make your database faster.

You notice your application is slow and want to find which database queries cause delays.

You want to improve database performance by optimizing the slowest queries.

You want to monitor query performance regularly to catch problems early.

You are debugging a new feature and want to check if queries run efficiently.

You want to compare query speeds before and after changes to your database or code.

Syntax

PostgreSQL

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

This query shows the top 5 slowest queries by total execution time.

pg_stat_statements must be enabled in PostgreSQL to use this view.

Examples

Shows the top 3 queries with the highest total execution time.

PostgreSQL

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

Shows the 5 queries with the highest average time per call, but only those run more than 10 times.

PostgreSQL

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

Finds the single query that took the longest time in any single execution.

PostgreSQL

SELECT query, max_time
FROM pg_stat_statements
ORDER BY max_time DESC
LIMIT 1;

Sample Program

This query lists the top 3 slowest queries by total time spent running. It helps you quickly see which queries use the most time overall.

PostgreSQL

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

Important Notes

You must enable the pg_stat_statements extension with CREATE EXTENSION pg_stat_statements; before using it.

Reset statistics with SELECT pg_stat_statements_reset(); if you want to clear old data.

pg_stat_statements tracks queries by normalizing them, so similar queries with different values are grouped together.

Summary

pg_stat_statements helps find slow queries by showing execution times and counts.

Use it to identify and fix queries that slow down your database.

Remember to enable the extension and reset stats when needed.