postgres error: canceling statement due to statement timeout - how to find the slow 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.

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