Long-running queries (PostgreSQL)

PostgreSQLRead onlyScripts Gallery

The queries running for longer than a number of seconds, your own session excluded.

Verification status. Written from the BroadSQL documentation and the PostgreSQL documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

Find what slows the database down now.
Check that a batch job is still running.

The Script

Download pg-long-running-queries.sql

-- @description: Queries running for longer than a number of seconds (PostgreSQL)
-- @tags: postgresql, diagnostics, performance, incident
-- @status: draft
-- @access: read
-- @dialect: PostgreSQL
-- @param min_seconds INTEGER = 60 "Shortest running time listed, in seconds"
SELECT pid,
       usename,
       state,
       (now() - query_start)::text AS running_for,
       LEFT(query, 200) AS query
  FROM pg_stat_activity
 WHERE state <> 'idle'
   AND pid <> pg_backend_pid()
   AND query_start < now() - make_interval(secs => ${min_seconds})
 ORDER BY query_start;

Parameters

Parameter	Type	Default	Meaning
`min_seconds`	`INTEGER`	`60`	Shortest running time listed, in seconds

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/pg-long-running-queries.sql.
Connect to the PostgreSQL database with an account that can see other sessions.
Check what it declares: LIB SHOW gallery/pg-long-running-queries.sql;
Run it: @gallery/pg-long-running-queries.sql min_seconds=300;

What you get

One row per session that is not idle and whose current query started more than min_seconds ago, oldest first.

Limits

The same visibility rules as for blocked sessions apply.
query holds at most the first 200 characters here, and PostgreSQL itself truncates long query texts (track_activity_query_size).

Found something wrong or unclear on this page? Tell us, with the page address.