pg_slow_query_digest — Find Slow Queries Before They Become Problems — Blog

What Does This Tool Do?

pg_slow_query_digest analyzes query statistics collected in pg_stat_statements and returns a list of the slowest queries sorted by total execution time. Unlike a simple SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC, the tool normalizes output, computes per-call averages, and identifies queries that consume the most resources through their frequency × duration product.

How Is It Called?

Connecting First

Before calling any performance tool, you need to connect to the target database. mcp-postgres supports multiple database profiles — ideal for multi-cluster setups:

● ● ● Claude CLI — Connection

claude> Connect using profile "production-db"

▶ Calling tools...
→ pg_connect { "profile": "production-db" }

{
"status": "connected",
"database": "mcp_test",
"version": "PostgreSQL 16.x",
"host": "db.example.com"
}

Claude CLI (Natural Language)

● ● ● Claude CLI — Performance Analysis

claude> Show me the slowest queries from the last hour

▶ Calling tools...
→ pg_slow_query_digest {}

Here are the 2 slowest queries by total execution time:

1. SELECT * FROM app.orders WHERE status = $1
   847 calls, avg 12.4ms, max 89.2ms, total: 10.5s
   → Candidate for an index on app.orders(status)

2. SELECT u.*, o.* FROM app.users u JOIN app.orders o...
   234 calls, avg 8.7ms, max 45.1ms, total: 2.0s
   → Join query with high row count per call (12)

MCP Protocol (Programmatic)

● ● ● MCP Protocol — Performance Analysis

// Programmatic invocation via MCP SDK
const result = await client.callTool("pg_slow_query_digest", {});

// Response:
{
"slow_queries": [
    {
      "queryid": "9069661048250515891",
      "query_preview": "ANALYZE",
      "calls": "3",
      "total_time_ms": "1522.52",
      "mean_time_ms": "507.51",
      "stddev_ms": "59.49",
      "rows": "0",
      "pct_total": "46.76"
    },
    {
      "queryid": "-8526373234667933857",
      "query_preview": "DO $$\nBEGIN\n FOR i IN 1..20 LOOP\n PERFORM count(*) FROM perf.transactions...",
      "calls": "1",
      "total_time_ms": "510.39",
      "mean_time_ms": "510.39",
      "stddev_ms": "0.00",
      "rows": "0",
      "pct_total": "15.68"
    },
    {
      "queryid": "3348334810192286589",
      "query_preview": "INSERT INTO perf.transactions (transaction_id, account_id, ...)...",
      "calls": "1",
      "total_time_ms": "483.69",
      "mean_time_ms": "483.69",
      "stddev_ms": "0.00",
      "rows": "100000",
      "pct_total": "14.86"
    },
    {
      "queryid": "4800951176609648582",
      "query_preview": "ANALYZE perf.transactions",
      "calls": "1",
      "total_time_ms": "110.56",
      "mean_time_ms": "110.56",
      "stddev_ms": "0.00",
      "rows": "0",
      "pct_total": "3.40"
    }
]
}

What Happens Under the Hood?

The tool executes this SQL query against pg_stat_statements:

-- Aggregated query statistics from pg_stat_statements
SELECT queryid, query AS query_preview, calls, total_exec_time AS total_time_ms, total_exec_time / calls AS mean_time_ms, stddev_exec_time AS stddev_ms, rows, total_exec_time * 100.0 / sum(total_exec_time) OVER() AS pct_total FROM pg_stat_statements WHERE calls > 0 ORDER BY total_exec_time DESC LIMIT 20;

Prerequisite: The pg_stat_statements extension must be loaded (shared_preload_libraries = 'pg_stat_statements'). The tool checks automatically and returns a clear error if the extension is unavailable.

Output in Detail

queryid — Unique ID of the normalized query from pg_stat_statements
query_preview — Preview of the query (truncated for long statements)
calls — How often the query was called since the last reset
total_time_ms — Total execution time in milliseconds — the sorting criterion
mean_time_ms — Average execution time per call
stddev_ms — Standard deviation — high values indicate unstable performance
rows — Total number of rows returned
pct_total — Percentage of total execution time across all queries

Pro Tip: A query with high total_ms but low mean_ms isn't slow per individual call — it's expensive due to sheer call volume. Connection pooling or query caching often helps more than an index here.

What Does the Test Suite Validate?

The test suite validates pg_slow_query_digest against a PostgreSQL 16 instance with 300,000 financial transactions in the perf schema with realistic query patterns:

● ● ● vitest run — Testsuite

✓ pg_slow_query_digest > returns query statistics
✓ pg_slow_query_digest > includes calls, mean_ms, max_ms fields
✓ pg_slow_query_digest > sorts by total execution time descending
✓ pg_slow_query_digest > respects limit parameter

Test Files 1 passed (1)
Tests 4 passed (4)
Duration 312ms

Test Data Scenario

The seed database contains:

perf.transactions — 300,000 rows (33 MB) with amount, currency, status, region, channel
pg_stat_statements — Automatically populated by the test runs themselves

Tests validate that:

The result is an array of query objects (not empty)
Each object contains expected fields (calls, mean_time_ms, pct_total)
Sorting by total_time_ms descending is correct
The limit parameter caps the result set

Summary

pg_stat_statements

Data Source

Output Fields

Tests

read-only

Type

pg_slow_query_digest is the first tool a DBA should reach for when investigating performance issues. It replaces manual digging through pg_stat_statements with a structured, sorted, and immediately actionable output.

Combined with pg_index_advisor (→ missing indexes) and pg_cache_hit_ratio (→ buffer efficiency), it forms the core of the /pg-perf workflow skill.

pg_slow_query_digest — Find Slow Queries Before They Become Problems