PostgreSQL - Performance Queries

 

Query to Find Long-Running Queries

This SQL query returns queries that have been running for more than a specific duration. 

Here, 60 seconds (1 minute) is used as an example, but you can adjust the interval as needed:

SELECT

    pid,

    usename,

    datname,

    state,

    query,

    NOW() - query_start AS duration

FROM

    pg_stat_activity

WHERE

    state = 'active'

    AND NOW() - query_start > interval '60 seconds'

ORDER BY

    duration DESC;

---------------------------

Alternative to Find All Running Queries

To view all running queries, regardless of duration, you can simply remove the time condition:

SELECT

    pid,

    usename,

    datname,

    state,

    query,

    NOW() - query_start AS duration

FROM

    pg_stat_activity

WHERE

    state = 'active'

ORDER BY

    duration DESC;

-------------------------------------------------------------

postgres=# select count(*),usename,state from pg_stat_activity group by usename, state;

 count | usename  | state

-------+----------+--------

     5 |          |

     1 | sam      | idle

     1 | postgres |

     1 | postgres | active

     7 | postgres | idle

     1 | repuser  | active

(6 rows)

--------------------------------------------------------------------

Locking session in PostgreSQL :-

-----------------------------------

SELECT 

    blocked_locks.pid AS blocked_pid,

    blocked_activity.usename AS blocked_user,

    blocking_locks.pid AS blocking_pid,

    blocking_activity.usename AS blocking_user

FROM 

    pg_catalog.pg_locks blocked_locks

JOIN 

    pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid

JOIN 

    pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database

    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

    AND blocking_locks.virtualtransaction IS NOT DISTINCT FROM blocked_locks.virtualtransaction

JOIN 

    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

WHERE 

    NOT blocked_locks.granted;

Kill the Blocking Session: 

-----------------------------------

If a transaction is blocking other sessions indefinitely, 

you might need to terminate the blocking session using:

SELECT pg_terminate_backend(blocking_pid);

Common Wait Events in PostgreSQL

--------------------------------------

Lock Waits: This occurs when a session is waiting for a lock held by another session. Common types include:

RowLock: Waiting for a row-level lock.

TableLock: Waiting for a table-level lock.

IO Waits: This indicates that a session is waiting for input/output operations to complete. For instance:

BufferIO: Waiting for data to be read from or written to disk.

Network Waits: These wait events occur when sessions are waiting for data over the network.

Timeout Waits: This happens when a session waits for an operation to complete but exceeds the specified timeout.

Checking Wait Events

----------------------

To analyze wait events in your PostgreSQL instance, you can query the pg_stat_activity view, 

which provides information about the current activity of all sessions:

SELECT 

    pid,

    usename,

    state,

    wait_event_type,

    wait_event,

    query

FROM 

    pg_stat_activity

WHERE 

    state = 'active'; 

-- or other filters based on your needs

This query will show you the session IDs (PIDs), usernames, states, wait event types, and the queries that are currently running.

Example of Analyzing Wait Events

----------------------------------------

If you want to specifically see which sessions are waiting for locks, you can use:

SELECT 

    blocking.pid AS blocking_pid,

    blocked.pid AS blocked_pid,

    blocked.query AS blocked_query,

    blocked.wait_event_type,

    blocked.wait_event

FROM 

    pg_catalog.pg_locks blocked_locks

JOIN 

    pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid

JOIN 

    pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database

    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

JOIN 

    pg_catalog.pg_stat_activity blocking ON blocking.pid = blocking_locks.pid

WHERE 

    NOT blocked_locks.granted;