I am researching heavy loads within the database when a user runs heavy queries. I got to pg_stat_statements, which shows me the id of the user, the query, and the time it takes to run. On the other hand I found pg_stat_activity that shows me the active users to the database with their pid, query_start, etc. I joined the 2 as they have similarities in user id.
SELECT a.usesysid,a.pid, a.client_addr, a.query_start, st.query, st.total_time
FROM pg_stat_activity as a
JOIN pg_stat_statements as st
ON a.usesysid = st.userid
this way I get the active user and where he used the query, like its load, etc. But the problem is when it is the same user connected with a different pid, the querys found are duplicated to each user pid that is connected at that moment.
In pg_stat_activity, it shows the user's data being active, therefore, if he disconnects his data is lost (I solved it with an ETL). but being active, how could it prevent them from being duplicated and taking their respective query used by the user?
any suggestion or documentation is welcome.
Don't join them. While you can, it is entirely unclear what you hope to accomplish by doing so. If you want to know what is loading the system right now, use pg_stat_activity. If you want to know in general what causes load averaged over time, use pg_stat_statements. And if you want to know what specific query executions took a long time historically, set up auto_explain with log_min_duration and then pull them out of the log file.