Horton

Thread

  1. empty,query_id, pg_stat_activity

    zhouenbing <zhouenbing@sunwoda-evb.com> — 2025-09-02T09:25:31Z

    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;

  2. Re: empty,query_id, pg_stat_activity

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-09-02T15:11:01Z

    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

  3. 答复: empty,query_id, pg_stat_activity

    zhouenbing <zhouenbing@sunwoda-evb.com> — 2025-09-03T02:00:13Z

    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

  4. Re: empty,query_id, pg_stat_activity

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-09-03T20:54:27Z

    On Tue, Sep 2, 2025 at 10:01 PM zhouenbing <zhouenbing@sunwoda-evb.com>
wrote:

> 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
>

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.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

  5. Re: empty,query_id, pg_stat_activity

    Michael Paquier <michael@paquier.xyz> — 2025-09-04T00:25:00Z

    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

  6. 答复: empty,query_id, pg_stat_activity

    zhouenbing <zhouenbing@sunwoda-evb.com> — 2025-09-04T03:32:18Z

    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.

  7. Re: empty,query_id, pg_stat_activity

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-09-04T12:56:05Z

    On Wed, Sep 3, 2025 at 11:32 PM zhouenbing <zhouenbing@sunwoda-evb.com>
wrote:

> 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.
>

I understand your point, but "active" for pg_stat_activity means it is
alive and doing something - just not what you are expecting in this case.
We are not going to add a new status, or change it to inactive. You can
think of the status you want (actively executing a query) as being
indicated by the combination of state=active and queryid not null.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support