Обсуждение: empty,query_id, pg_stat_activity
Hello, everyone
When I was studying the current SQL performance issues in Postgres, I found that some queryids in pg_stat_activity were empty. My other DBA colleagues had sent emails to consult the community, and the community replied that this was not a bug. However, this empty queryid problem will affect our ability to effectively track the SQL performance issues of the current session. For example, some SQL statements are too long in text and take too long to execute. Without queryid, it is very difficult for me to accurately locate which SQL statement is being executed in the active session in pg_stat_statements? At the same time, it is also very difficult to continue to effectively track the relevant execution status of the current active session's SQL.
Session 1: pgbentch –I –s 10 testdb
pgbentch -T1200 testdb
session 2: psql –t<<EOF
select pid,query_id,state,query from pg_stat_activity where pid=510506;
\watch 0.2
EOF
pid | query_id | state | query
--------+----------------------+--------+-------
510506 | 3130448535078843330 | active | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (65, 6, 910745, -4761, CURRENT_TIMESTAMP);
510506 | | active | UPDATE pgbench_accounts SET abalance = abalance + -2452 WHERE aid = 516851;
510506 | | active | SELECT abalance FROM pgbench_accounts WHERE aid = 279298;
510506 | 447485537170305879 | active | UPDATE pgbench_tellers SET tbalance = tbalance + -3450 WHERE tid = 73;
No, if the query_id I provided is empty, it indicates that the statement session is in an active state, meaning the operation is ongoing. If I add "WHERE query_id IS NOT NULL" according to your method to exclude the sessions with empty queryids, there will be a problem: "Suppose there are several abnormal SQLs currently executing in the database server, causing the server's IO to be excessively high, and the corresponding query_id for these SQLs is empty. Then, I will be unable to track down that abnormal SQL. Therefore, I think this issue with the query_id might not be a bug, but it needs to be improved and fixed to assist the DBA in quickly analyzing and locating the SQL performance issues.
Session is activity When queryid is null ：
Session 1: pgbentch –I –s 10 testdb
pgbentch -T1200 testdb
session 2: psql –t<<EOF
select pid,query_id,state,query from pg_stat_activity where pid=510506;
\watch 0.2
EOF
pid | query_id | state | query
--------+----------------------+--------+-------
510506 | 3130448535078843330 | active | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (65, 6, 910745, -4761, CURRENT_TIMESTAMP);
510506 | | active | UPDATE pgbench_accounts SET abalance = abalance + -2452 WHERE aid = 516851;
510506 | | active | SELECT abalance FROM pgbench_accounts WHERE aid = 279298;
510506 | 447485537170305879 | active | UPDATE pgbench_tellers SET tbalance = tbalance + -3450 WHERE tid = 73;
发件人: Greg Sabino Mullane [mailto:htamfids@gmail.com]
发送时间: 2025-09-02 23:11
收件人: zhouenbing <zhouenbing@sunwoda-evb.com>
抄送: pgsql-bugs@lists.postgresql.org
主题: Re: empty,query_id, pg_stat_activity
If the query_id* is null, it is not actually running yet. This is a good thing as far as your stated tracking requirements. You can do a WHERE query_id IS NOT NULL to exclude statements where the query has appeared, but has not been parsed and executed yet. I daresay you also want to be looking at wait_event and now()-state_change as well.
Cheers,
Greg
* Actually, query_id some places, queryid in others. So annoying.
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
No, if the query_id I provided is empty, it indicates that the statement session is in an active state, meaning the operation is ongoing
On Wed, Sep 03, 2025 at 04:54:27PM -0400, Greg Sabino Mullane wrote: > The session is active, but there is no I/O yet, as the query has arrived, > but has not been parsed yet. The queryid appears right before the query > actually executes, so a lack of queryid is a safe indicator that the query > is not actually doing any work yet- it's still spinning up. From a > performance monitoring standpoint, you should be safe to exclude rows with > no queryid. Yeah, and there's likely no point in feeding the data of pg_stat_activity to a CTAS, something that pg_stat_statements does for most people these days. FWIW, I use queries based on pg_stat_activity for monitoring purposes in benchmarks to get aggregated values about wait events, to be able to grab patterns, most of the time. Filtering out query IDs would lead to less information gathered. You did not mention why pg_stat_activity coupled with a query ID would be useful for you, especially since we have a bunch of pgstats views that are able to get aggregated data for the user, as well, to grab for bottlenecks, like pg_stat_io, etc. -- Michael
Вложения
hello Michael
The application scenario for performing performance analysis on
Postgres through query_id is as follows:
First, monitor the events and query_ids of the active database sessions
during the current or past period by using the pg_stat_activity or
pg_active_session_history views to analyze performance issues. Then, quickly
locate the detailed execution information of the SQL statements by using the
query_id of the session SQL in the pg_stat_statements.
Aggregate and statistically analyze the performance of the SQL at different
time points in the past by aggregating in pg_stat_statements_history within
the specified time period.
However, at present, some active sessions in pg_stat_activity have empty
query_ids, which leads to many query_ids being empty in
pg_active_session_history as well. If Mr. Greg said that empty query_ids
indicate that the session SQL has not been executed, then I will exclude the
sessions with empty query_ids. But I think that if the SQL has not been
executed, the session status should be 'inactive' instead.
pg_active_session_history as well. If Mr. Greg said that empty query_ids
indicate that the session SQL has not been executed, then I will exclude the
sessions with empty query_ids. But I think that if the SQL has not been
executed, the session status should be 'inactive' instead.