Troubleshooting - PostgreSQL Session CPU Analyze

Hello,

I have created a simple structure to monitor the pg_stat_activity table on
the database. There's no issue with this; I can log it as desired.

However, our main need is to see the CPU usage percentage consumed by each
session. You can think of it as an analysis similar to the spWhoIsActive
method in SQL Server.

However, I cannot determine the load caused by a specific query on the
server at a given moment.

Even if I try to match PIDs on the system, this approach might not yield
accurate results due to interventions like session kills during the check.

I would appreciate it if you could share your experiences and
recommendations on this matter.

In standard processes, I can perform real-time analyses using the following
query:

SELECT
pss.userid,
pss.dbid,
pd.datname AS db_name,
round(pss.total_exec_time::numeric, 2) AS total_exec_time,
pss.calls,
round(pss.mean_exec_time::numeric, 2) AS mean_exec_time,
round((100 * pss.total_exec_time / sum(pss.total_exec_time::numeric)
OVER ())::numeric, 2) AS cpu_portion_pctg,
pss.query,
psa.client_addr AS client_host
FROM
pg_stat_statements pss
JOIN
pg_database pd ON pd.oid = pss.dbid
JOIN
pg_stat_activity psa ON psa.query = pss.query
ORDER BY
pss.total_exec_time DESC
LIMIT
30;

When I attempt to log this query, it will only provide statistical
responses, meaning the differences will not yield 100% accurate results on
the actual server.

Have a nice day!