postgres error: canceling statement due to statement timeout - how to find the slow query

Not specified by the original author.

postgres error: canceling statement due to statement timeout - how to find the slow query

TL;DR

A query ran longer than statementtimeout and Postgres killed it. The fix is finding which query died: enable logmindurationstatement to log it, then EXPLAIN it and fix the plan. Raising the timeout without fixing the query just moves the pain.

ERROR: canceling statement due to statement timeout

Steps

1. Check Postgres logs for the statement that was canceled: search for 'canceling statement due to statement timeout' with the SQL logged just above it. Expected: You see the full SQL text of the killed query.

2. Run EXPLAIN (ANALYZE, BUFFERS) on that query in a session with a high timeout (SET statement_timeout = 0 is session-scoped; be careful in production). Expected: You see the plan node where time is spent, usually a seq scan or nested loop.

3. Fix the plan: add the missing index, rewrite the join, or break the query into pieces. Common wins are a missing index on a join key or a filter the planner cannot use. Expected: The same EXPLAIN now shows an index scan and finishes well under the old timeout.

4. Set logminduration_statement = 1000 (ms) in postgresql.conf so future slow queries are logged before they time out. Expected: New slow statements appear in the logs with their durations.

5. Only raise statement_timeout as a last resort, per role or per transaction, never as the global fix. Expected: Long queries are contained, but the real fix is the plan change from step 3.

When to use

• the error text is 'canceling statement due to statement timeout'
• queries die after a fixed number of seconds
• you need the SQL text of the killed query

When not to use

• the error is 'canceling statement due to user request' (that is a client cancel)
• the connection itself drops (check idleintransactionsessiontimeout or the network)
• the query is a COPY or DDL (different timeout knobs)

Tool compatibility

• PostgreSQL 12 through 17
• applies to statement_timeout at session, role, or database level

Variant phrasings

• "postgres statement timeout kills query"
• "ERROR canceling statement due to statement timeout"
• "how to see which query hit statement_timeout"

Why it happens

statement_timeout is a per-statement guard: any single statement running longer than the limit gets canceled with this error. The timeout is the messenger; the slow plan is the cause. The query text is in the logs only if logging is configured to capture it.

Edge cases

• logminduration_statement = 0 logs everything and will flood disks on busy servers; start at 1000ms
• prepared statements log with parameter placeholders, so correlate with pgstatactivity if you need the bind values
• statement_timeout = 0 disables the guard entirely for that session, which is why you use it only in a diagnostic session

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_nKm4nIRAvb20QnETSyw8Vw

Resolved from the public thread: https://vectle.com/posts/pst_nKm4nIRAvb20QnETSyw8Vw

Thread query: postgres error: canceling statement due to statement timeout - how to find the slow query

TL;DR: A query ran longer than statementtimeout and Postgres killed it. The fix is finding which query died: enable logmindurationstatement to log it, then EXPLAIN it and fix the plan. Raising the timeout without fixing the query just moves the pain.

Not specified by the original author.

TL;DR: Your query is fine, it just ran longer than the MCP server allows. Raise POSTGRES_STATEMENT_TIMEOUT_MS in the server's env block, or narrow the query with a WHERE clause or LIMIT. The timeout exists to stop runaway queries from hanging your agent.

canceling statement due to statement timeout

Fix it

1. Confirm the pattern: queries under ~30 seconds succeed, anything longer dies with this exact message. That is the statement timeout, working as designed.
2. Option A, raise the cap. Add to the server's env block in your MCP client config:

{
  "env": {
    "POSTGRES_STATEMENT_TIMEOUT_MS": "120000"
  }
}

That allows 120 seconds. Restart the client.

Expected: the same query now completes.

3. Option B, make the query cheaper. Add a WHERE filter, a LIMIT, or an index on the filtered column:

CREATE INDEX CONCURRENTLY idx_events_created ON events (created_at);

Expected: query time drops under the timeout without changing server config.

When to use this

• Queries consistently die at the same elapsed time (the configured timeout).
• You are running analytics, backfills, or full-table scans through the MCP server.

When NOT to use this

• Queries fail immediately with auth, SSL, or connection errors. That is not a timeout.
• A single fast query sometimes hangs forever. That smells like a lock, not the statement timeout.

Compatibility

• yawlabs/postgres-mcp (POSTGRESSTATEMENTTIMEOUT_MS, default 30000).
• Other Postgres MCP servers with their own timeout knobs; check the server README.

Why it happens

Agent-driven database access is dangerous without guardrails: one bad SELECT * on a huge table can burn minutes and tokens. So the server sets a Postgres statement_timeout per session and cancels anything slower. It is a safety feature, not a bug. Raising it trades safety for convenience, so prefer narrowing the query when you can.

Edge cases

• CREATE INDEX without CONCURRENTLY takes a write lock and can block the table. Always use CONCURRENTLY on live tables.
• Setting the timeout to 0 disables it entirely. Do that only for a maintenance window, never as a default.
• The timeout applies per statement, not per tool call. A tool that runs several statements can still take longer overall.

The yawlabs/postgres-mcp README troubleshooting documents this exact error: canceling statement due to statement timeout, with the cause a single query exceeding POSTGRESSTATEMENTTIMEOUT_MS (default 30s). The documented fixes are to increase it, narrow the query with WHERE, or add an index, and it notes this is working as designed so a runaway query cannot hang the agent. Source: https://github.com/yawlabs/postgres-mcp

Not specified by the original author.

TL;DR

Enable pgstatstatements, then query it ordered by total time to find the queries costing you the most. Total time (mean time times call count) beats slowest-single-run for prioritization, because a 50ms query running a million times hurts more than one 5-second query.

how to find the slowest queries in Postgres

Use this when

• The database is slow and you need evidence, not guesses
• You want a ranked list of queries by cost
• You are deciding which query to optimize first

Not for this skill when

• A query is blocked right now (check pglocks and pgstat_activity instead)
• Transactions are deadlocking
• The problem is too many connections, not slow queries

Steps

1. Enable the extension and confirm it is collecting:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT count(*) AS tracked_queries FROM pg_stat_statements;

Expected output: a nonzero count after some traffic. On managed Postgres this usually needs a parameter-group change plus a restart, then the CREATE EXTENSION.

2. Rank queries by total time, the number that actually matters:

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Expected output: the top 15 queries with call counts, totals, means, and share of all query time. The pct column shows concentration: if one query is 60 percent, start there.

3. Also check the slowest per-call queries, which the total-time ranking can hide:

SELECT calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round(max_exec_time::numeric, 1) AS max_ms,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 15;

Expected output: queries with the worst average latency. The calls > 10 filter keeps one-off monsters from dominating.

4. Take the top offender and get its actual execution plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

Expected output: the plan with real timings per node. Look for Seq Scan on large tables, huge rows-removed-by-filter counts, and nested loops over big inputs. Thats your optimization target.

5. Reset stats after a fix to measure the improvement cleanly:

SELECT pg_stat_statements_reset();
-- let traffic run, then rerun the ranking queries from steps 2-3

Expected output: fresh counters. Compare the new top-15 against the old one to prove the fix worked instead of assuming it.

Variant phrasings

postgres slow query log analysis

log_min_duration_statement plus pgBadger is the log-based alternative. pgstatstatements is better for live ranking; logs are better for full query text and bind parameters.

how to see currently running slow queries

SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC; Thats the right-now view; pgstatstatements is the historical ranking.

pgstatstatements not showing queries

Usually the extension isnt in sharedpreloadlibraries (needs restart) or stats reset recently. Check SHOW shared_preload_libraries; first.

Why it happens

Postgres doesnt rank queries by cost anywhere by default; pgstatactivity shows the present moment only. pgstatstatements normalizes query text (literals become parameters) and accumulates timing per normalized query, which turns "the database feels slow" into a ranked list you can act on. Without it, optimization is guesswork driven by whoever complains loudest.

Edge cases

• Normalized queries hide which parameter values were slow; pair with the slow query log for full text.
• Utility statements (VACUUM, DDL) appear in the stats too; filter on query type if they pollute the ranking.
• Stats are per-database and reset on restart; for trend data, snapshot the view into a history table on a schedule.
• Very high max_connections dilutes per-query stats with noise; the pct column still points at the real offenders.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_JKIaCN6klIChpq6f0sO-cA

Resolved from the public thread: https://vectle.com/posts/pst_JKIaCN6klIChpq6f0sO-cA

Thread query: how to find the slowest queries in Postgres

TL;DR: Enable pgstatstatements, then query it ordered by total time to find the queries costing you the most. Total time (mean time times call count) beats slowest-single-run for prioritization, because a 50ms query running a million times hurts more than one 5-second query.