查看某个语句的当前等待事件可以查询pg_stat_activity表（类似于Oracle的v$session），如下所示：
select wait_event_type,wait_event,state,query,a.* from pg_stat_activity a where query like 'insert%';
wait_event_type|wait_event|state |query |datid |datname|pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event|state |backend_xid|backend_xmin|query |backend_type |
---------------+----------+------+---------------------------------------------------------------------+------+-------+-----+----------+--------+-------+------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+----------+------+-----------+------------+---------------------------------------------------------------------+--------------+
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test|24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql>|172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18| | |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
state等于active，没有等待事件。此时唯一的可能是在做计算密集型操作。可以通过pstack查看PID查看语句在做什么，如下：
[zjh@lightdb1 pgsql13.2]$ pstack 24870
#0 0x000000000049553c in ginTraverseLock ()
#1 0x0000000000495cf9 in ginFindLeafPage ()
#2 0x0000000000498764 in ginInsertItemPointers ()
#3 0x000000000049e670 in ginEntryInsert ()
#4 0x000000000049a7c4 in ginInsertCleanup ()
#5 0x000000000049b1e8 in ginHeapTupleFastInsert ()
#6 0x000000000049ee98 in gininsert ()
#7 0x000000000061b8aa in ExecInsertIndexTuples ()
#8 0x00000000006445fa in ExecInsert ()
#9 0x0000000000645949 in ExecModifyTable ()
#10 0x000000000061c482 in standard_ExecutorRun ()
#11 0x00007f1da44e5f97 in pgss_ExecutorRun (queryDesc=0x1cac8f8, direction=ForwardScanDirection, count=0, execute_once=true) at pg_stat_statements.c:1045
#12 0x0000000000772bda in ProcessQuery ()
#13 0x0000000000772e14 in PortalRunMulti ()
#14 0x000000000077382d in PortalRun ()
#15 0x0000000000771412 in PostgresMain ()
#16 0x0000000000483069 in ServerLoop ()
#17 0x00000000006fe0b3 in PostmasterMain ()
#18 0x0000000000483cae in main ()
注：上述示例中，除了pg_stat_statements插件外，pg没有使用-g选项编译，所以缺少源文件信息。可通过pg_config（或lightdb lt_config）查看编译选项，该选项是后续所有基于本实例的插件的编译选项要求。
原来在更新gin索引。
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+-------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+-------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |DataFileWrite|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |DataFileWrite|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18| | |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event|state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event|state |backend_xid|backend_xmin|query |backend_type |
---------------+----------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+----------+------+-----------+------------+---------------------------------------------------------------------+--------------+
LWLock |WALWrite |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|LWLock |WALWrite |active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead|idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead|idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
LWLock |WALWrite |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|LWLock |WALWrite |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |DataFileRead|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |DataFileRead|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18| | |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+--------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+--------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |DataFileExtend|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |DataFileExtend|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
IO |DataFileRead |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|IO |DataFileRead |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+-------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+-------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
| |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20| | |active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
LWLock |BufferContent|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|LWLock |BufferContent|active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
wait_event_type|wait_event |state |query |datid |datname |pid |leader_pid|usesysid|usename|application_name |client_addr |client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event |state |backend_xid|backend_xmin|query |backend_type |
---------------+------------+------+---------------------------------------------------------------------+------+--------+-----+----------+--------+-------+-------------------------------------------+------------+---------------+-----------+-------------------+-------------------+-------------------+-------------------+---------------+------------+------+-----------+------------+---------------------------------------------------------------------+--------------+
IO |WALInitWrite|active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |25669| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-100.sql>|172.24.32.48| | 53166|2021-09-06 21:41:58|2021-09-06 21:42:20|2021-09-06 21:42:20|2021-09-06 21:42:20|IO |WALInitWrite|active|12755338 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
Client |ClientRead |idle |insert into big_search_doc_new_ic select * from big_search_doc_new_ic| 13580|postgres|24877| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-98.sql> |172.24.32.48| | 53062|2021-09-06 21:34:52| |2021-09-06 21:41:49|2021-09-06 21:41:49|Client |ClientRead |idle | | |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
LWLock |WALWrite |active|insert into big_search_doc_new_ic select * from big_search_doc_new_ic|107265|es_test |24870| | 10|zjh |DBeaver 21.1.5 - SQLEditor <Script-97.sql> |172.24.32.48| | 53056|2021-09-06 21:34:52|2021-09-06 21:35:18|2021-09-06 21:35:18|2021-09-06 21:35:18|LWLock |WALWrite |active|12755337 |12755337 |insert into big_search_doc_new_ic select * from big_search_doc_new_ic|client backend|
这个信息是PG官方手册和DBA不会告诉你的。所以如果不在extension中，实际上你可以将它归类为CPU Time（lightdb 22.1将分解extension\db time\idle）。
参考：
https://www.postgresql.org/docs/14/monitoring.html
oracle 11gr2 null event等待事件