丁久

Posted on May 16 • Originally published at dingjiu1989-hue.github.io

Slow Query Troubleshooting: Identification, Profiling, and Optimization

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Slow Query Troubleshooting: Identification, Profiling, and Optimization

Slow queries are the most common database performance problem. This article presents a systematic workflow: from identifying the slowest queries through profiling to implementing and verifying optimizations.

Step 1: Identify Slow Queries

Using pg_stat_statements

Enable the extension and query for the worst performers:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Top 10 queries by total execution time

SELECT queryid,

LEFT(query, 100) AS query_preview,

calls,

ROUND(total_exec_time::numeric, 2) AS total_ms,

ROUND(mean_exec_time::numeric, 2) AS avg_ms,

ROUND(min_exec_time::numeric, 2) AS min_ms,

ROUND(max_exec_time::numeric, 2) AS max_ms,

ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,

rows,

shared_blks_hit,

shared_blks_read,

shared_blks_dirtied,

shared_blks_written

FROM pg_stat_statements

WHERE query NOT LIKE '%pg_stat%'

ORDER BY total_exec_time DESC

LIMIT 20;

Focus on queries with:

High total_exec_time: Consuming the most database time overall.
High mean_exec_time: Individually slow queries.
High stddev_exec_time: Performance varies wildly (plan instability).
Low cache hit ratio: (shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0)) < 0.99.

Using pg_stat_activity

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Current running queries

SELECT pid, now() - query_start AS duration, state, query

FROM pg_stat_activity

WHERE state = 'active'

AND query_start < now() - interval '5 seconds'

ORDER BY duration DESC;

External Tools

pgBadger: parse PostgreSQL logs for slow queries

pgbadger /var/log/postgresql/postgresql.log -o report.html

pgFincore: analyze cache hit rates

pgbouncer logs for pool-level insights

Step 2: Profile the Problem Query

Once identified, profile the slow query:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Reset statistics for this specific query

SELECT pg_stat_statements_reset();

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Run the query once

EXPLAIN (ANALYZE, BUFFERS, TIMING)

SELECT u.email, COUNT(o.id) AS order_count

FROM users u

LEFT JOIN orders o ON o.user_id = u.id

WHERE u.created_at > '2026-01-01'

GROUP BY u.email

ORDER BY order_count DESC

LIMIT 100;

What to Look For in the Plan

Seq Scan on a large table when only a few rows are needed → Add an index.

2\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\. Large discrepancy between estimated rows and actual rows → Run ANALYZE. 3\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Slow Query Troubleshooting: Identification, Profiling, and Optimization

Slow Query Troubleshooting: Identification, Profiling, and Optimization