現在走っているクエリを調べたい！
以前PostgreSQLの実行中のプロセスを止める方法について記事を書きましたが、
どのクエリが DB を重くしているかわからない時があるかと思います。
今日は PostgreSQL で現在実行中のクエリを調べる方法をご紹介します！
実行中のクエリを調べるクエリはこれだ！
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 <>''
ORDER BY
lap DESC;実行中のクエリを調べるクエリを実行するとこうなる
procpid | start | lap | current_query
---------+-------------------------------+----------+--------------------------------------------------------------------
4173 | 2014-06-16 10:26:52.111804+09 | 00:00:00 | 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 <>'' +
| | | ORDER BY +
| | | lap DESC;
(1 行)