查看PG某个进程对应的SQL语句

郑全

比如通过TOP看到某个进程CPU比较高，想看看该进程对应的SQL情况，可以使用下面的SQL语句查看，比如找到pid=3879272 占用资源比较多，可以通过下面的SQL语句查出到底在做什么？


SELECT procpid, START, now() - START AS lap, current_query
FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid,
              pg_stat_get_backend_activity_start (S.backendid) AS START,
              pg_stat_get_backend_activity (S.backendid) AS current_query
         FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S
      WHERE current_query <> '<IDLE>'
and procpid=3879272 ORDER BY lap DESC;

procpid |             start             |       lap       |                                              current_query                                               
---------+-------------------------------+-----------------+----------------------------------------------------------------------------------------------------------
3879272 | 2023-08-01 23:44:57.321246-04 | 00:00:10.359089 | select * from mdc2_mz_cost_view where patientid='61339' and clinicno='2307190625_103678' and hiscode='0'
(1 row)

如果有多个，直接使用IN

SELECT procpid, START, now() - START AS lap, current_query
FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid,
              pg_stat_get_backend_activity_start (S.backendid) AS START,
              pg_stat_get_backend_activity (S.backendid) AS current_query
         FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S
      WHERE current_query <> '<IDLE>'
and procpid in(3879276,3879283,3879274,3879265,3879277,3879278,38792623879263,3879280,3879259,3879261,3879271,3879284,3879264) ORDER BY lap DESC;

procpid |             start             |       lap       |                                              current_query                                               
---------+-------------------------------+-----------------+----------------------------------------------------------------------------------------------------------
3879265 | 2023-08-01 23:50:41.399739-04 | 00:00:36.963131 | select * from mdc2_mz_cost_view where patientid='7821' and clinicno='2306030170_100957' and hiscode='0'
3879264 | 2023-08-01 23:50:42.220297-04 | 00:00:36.142573 | select * from mdc2_mz_cost_view where patientid='32605' and clinicno='2306210204_102632' and hiscode='0'
3879283 | 2023-08-01 23:50:52.703433-04 | 00:00:25.659437 | select * from mdc2_mz_cost_view where patientid='39682' and clinicno='2306270685_101562' and hiscode='0'
3879259 | 2023-08-01 23:50:55.122623-04 | 00:00:23.240247 | select * from mdc2_mz_cost_view where patientid='60739' and clinicno='2307190034_102406' and hiscode='0'
3879280 | 2023-08-01 23:51:03.157923-04 | 00:00:15.204947 | select * from mdc2_mz_cost_view where patientid='17185' and clinicno='2306090472_102396' and hiscode='0'
3879284 | 2023-08-01 23:51:05.838177-04 | 00:00:12.524693 | select * from mdc2_mz_cost_view where patientid='54827' and clinicno='2307110866_103841' and hiscode='0'
3879261 | 2023-08-01 23:51:07.146341-04 | 00:00:11.216529 | select * from mdc2_mz_cost_view where patientid='48480' and clinicno='2307050484_102964' and hiscode='0'
3879277 | 2023-08-01 23:51:11.027748-04 | 00:00:07.335122 | select * from mdc2_mz_cost_view where patientid='57812' and clinicno='2307150472_103855' and hiscode='0'
3879271 | 2023-08-01 23:51:12.091143-04 | 00:00:06.271727 | select * from mdc2_mz_cost_view where patientid='60824' and clinicno='2307190102_102575' and hiscode='0'
3879274 | 2023-08-01 23:51:14.928943-04 | 00:00:03.433927 | select * from mdc2_mz_cost_view where patientid='61062' and clinicno='2307190345_103047' and hiscode='0'
3879276 | 2023-08-01 23:51:15.441895-04 | 00:00:02.920975 | select * from mdc2_mz_cost_view where patientid='61262' and clinicno='2307190552_103491' and hiscode='0'
3879278 | 2023-08-01 23:51:15.763607-04 | 00:00:02.599263 | select * from mdc2_mz_cost_view where patientid='60963' and clinicno='2307190246_102840' and hiscode='0'
(12 rows)