问题描述
最近生产上的 PostgreSQL 发现有几个进程一直长时间运行，通过 top 命令能看到很多对应 postgres 进程都跑到接近 100%，而且 TIME+ 时间很长。
最终找到了查看 CPU 执行时间过长是哪个语句。我这里当前使用的数据库版本是PostgreSQL 9.6.6。
查看占用 CPU 最多的几个 PostgreSQL 进程
# ps aux | grep postgres | sort -n -r -k 3 | head -10 | awk '{print $2, $3}'
查看 CPU 过高 PostgreSQL 进程的执行时间
postgres=# 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=22687 ORDER BY lap DESC;
procpid | start | lap | current_query
---------+------------------------------+-----------------+--------------------------------------------------------------------------------------------------------
22687 | 2019-04-09 10:57:21.96082+08 | 00:36:07.920674 | EXPLAIN ANALYSE SELECT a.id, +
| | | a.content, +
| | | a.entity_id, +
| | | a.entity_type, +
| | | a.start_date, +
| | | a.due_date, +
| | | a.image_id, +
| | | a.sg_status_list, +
| | | a.project_id, +
| | | a.cached_display_name, +
| | | a.dependency_violation, +
| | | a.sg_progress_info, +
| | | a.inventory_date, +
| | | a.step_id, +
| | | a.time_logs_sum, +
| | | a.est_in_mins, +
| | | a.sg_production_bid_id, +
| | | a.sg_production_bid_type, +
| | | a.pinned, +
| | | a.splits, +
| | | a.filmstrip_image_id, +
| | | c.cached_display_name AS _grp_c_cached_display_name, +
| | | CASE WHEN c.id IS NULL THEN NULL ELSE 'Project' END AS _grp__project_, +
| | | c.id AS _grp_c_id +
| | | FROM tasks a +
| | | LEFT JOIN display_name_caches b ON (b.entity_type = 'Project' AND b.entity_id = a.project_id)+
| | | LEFT JOIN projects c ON (c.id = a.project_id AND c.retirement_date IS NULL) +
| | | WHERE ((a.sg_disabled = 'f' AND (a.project_id = ANY +
只需要在查询语句中的 procpid 输入对应查询到的进程 id 即可，上面 SQL 语句中我查询的进程 id 号是 22687，上面的查询语句只是查询其中一个进程的结果。查询结果中几个字段的含义如下： procpid：进程id start：进程开始时间 lap：经过时间 current_query：执行中的sql
