View PostgreSQL Queries Using CPU and Their Corresponding SQL Statements

How to view the PostgreSQL queries consuming the most CPU?

ps aux | grep postgres | sort -n -r -k 3 | head -10 | awk '{print $2, $3}'

How to view the execution time of all PostgreSQL queries?

select procpid, start, now() - start as lap, current_query 
from 
(select backendid, pg_stat_get_backend_pid(s.backendid) as procpid, pg_stat_get_backend_activity_start(s.backendid) as start, pg_stat_get_backend_activity(s.backendid) as current_query 
from 
(select pg_stat_get_backend_idset() as backendid) as s 
) as s 
where 
current_query <> '<IDLE>' and procpid IN （17637,123,321） --Add the process ID you find
order by 
lap desc;

• procpid: Process ID

• start: Process start time

• lap: Elapsed time

• current_query: SQL query currently executing

How to Stop a Running SQL Query?

There are three ways to kill a running SQL query:

First Method:

SELECT pg_cancel_backend(PID);

This method only works for SELECT queries. It does not affect UPDATE, DELETE, or other DML operations.

Second Method:

SELECT pg_terminate_backend(PID);

This method can terminate various operations, including SELECT, UPDATE, DELETE, DROP, etc.

Third Method (Not Recommended):

kill -9 <process_id>;

Takeaway

Here is a script to view the top PostgreSQL queries consuming the most CPU:

ps aux | grep postgres | sort -n -r -k 3 | head -10 | awk '{print $2}' > /tmp/top.tmp
WHERE="1"
TMPFILE="/tmp/top.tmp"
cat $TMPFILE | while read line
do
        WHERE="$WHERE , $line"
        echo $WHERE > $TMPFILE
done
WHERE=`cat $TMPFILE`
psql -h Server address -U pdata -s *** -W 密码 -c"select procpid, start, now() - start as lap, current_query from (select backendid, pg_stat_get_backend_pid(s.backendid) as procpid, 
pg_stat_get_backend_activity_start(s.backendid) as start, pg_stat_get_backend_activity(s.backendid) as current_query from (select pg_stat_get_backend_idset() as backendid) as s ) as s where current_query <> '<IDLE>' and (procpid in ($WH
ERE))order by lap desc;