インフラエンジニアbacchiのわかったことまとめ

bacchi.me

【PostgreSQL】実行中のクエリを調べる方法

現在走っているクエリを調べたい!

以前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 行)
  • B!