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;