Helpful pg_stat_activity queries

Helpful pg_stat_activity queries 

Show all sessions ordered by state, with active_qry_runtime (the time the query has been activily running) and time_in_current_state (the time the session has been in its current state)

select pid, usename, application_name, wait_event_type, wait_event, state, CASE WHEN state in ('active','starting') THEN now()-query_start ELSE null END as active_qry_runtime, now()-state_change as time_in_current_state from pg_stat_activity order by state;

Show all sessions ordered by state

select pid, usename, application_name, wait_event_type, wait_event, state from pg_stat_activity order by state;

Active sessions and the query they are running

select pid, usename, application_name, wait_event_type, wait_event, state, query from pg_stat_activity where state = 'active'

basic query for pg_stat_statements

Select userid, substring(query,1,80), calls, mean_time, rows/calls from pg_stat_statements

****************************************** keywords: pg_stat ****************************************** rdbms version: postgres 15 ******************************************