psql で PostgreSQL にクエリを定期実行する

書式

クエリ \watch 秒数

例

select b.usename,
trunc(a.total_time) "total_time(s)",
a.calls,
trunc(cast(a.total_time as numeric) / a.calls, 6) "avg_time(s)",
a.query
from pg_stat_statements a join pg_user b
on a.userid = b.usesysid
where b.usename = 'awsuser'
order by total_time desc
limit 5; \watch 5


 usename | total_time(s) |  calls   | avg_time(s) |                               query
---------+---------------+----------+-------------+-------------------------------------------------------------------
 awsuser |       2414712 | 60245873 |    0.040080 | select nextval($1)
 awsuser |           319 |    31729 |    0.010081 | DISCARD ALL
 awsuser |           279 |    36406 |    0.007673 | SET application_name TO 'psql'
 awsuser |           164 |       36 |    4.561203 | select b.usename,                                                +
         |               |          |             | trunc(a.total_time) "total_time(s)",                             +
         |               |          |             | a.calls,                                                         +
         |               |          |             | trunc(cast(a.total_time as numeric) / a.calls, $1) "avg_time(s)",+
         |               |          |             | a.query                                                          +
         |               |          |             | from pg_stat_statements a join pg_user b                         +
         |               |          |             | on a.userid = b.usesysid                                         +
         |               |          |             | where b.usename = $2                                             +
         |               |          |             | order by total_time desc                                         +
         |               |          |             | limit $3
 awsuser |           120 |    26417 |    0.004561 | SELECT current_setting($1)
(5 rows)

                                         Wed Dec  2 02:43:14 2020 (every 5s)

 usename | total_time(s) |  calls   | avg_time(s) |                               query
---------+---------------+----------+-------------+-------------------------------------------------------------------
 awsuser |       2414712 | 60245873 |    0.040080 | select nextval($1)
 awsuser |           319 |    31729 |    0.010081 | DISCARD ALL
 awsuser |           279 |    36406 |    0.007673 | SET application_name TO 'psql'
 awsuser |           168 |       37 |    4.549036 | select b.usename,                                                +
         |               |          |             | trunc(a.total_time) "total_time(s)",                             +
         |               |          |             | a.calls,                                                         +
         |               |          |             | trunc(cast(a.total_time as numeric) / a.calls, $1) "avg_time(s)",+
         |               |          |             | a.query                                                          +
         |               |          |             | from pg_stat_statements a join pg_user b                         +
         |               |          |             | on a.userid = b.usesysid                                         +
         |               |          |             | where b.usename = $2                                             +
         |               |          |             | order by total_time desc                                         +
         |               |          |             | limit $3
 awsuser |           120 |    26417 |    0.004561 | SELECT current_setting($1)
(5 rows)

                                         Wed Dec  2 02:43:19 2020 (every 5s)

 usename | total_time(s) |  calls   | avg_time(s) |                               query
---------+---------------+----------+-------------+-------------------------------------------------------------------
 awsuser |       2414712 | 60245873 |    0.040080 | select nextval($1)
 awsuser |           319 |    31729 |    0.010081 | DISCARD ALL
 awsuser |           279 |    36406 |    0.007673 | SET application_name TO 'psql'
 awsuser |           172 |       38 |    4.537781 | select b.usename,                                                +
         |               |          |             | trunc(a.total_time) "total_time(s)",                             +
         |               |          |             | a.calls,                                                         +
         |               |          |             | trunc(cast(a.total_time as numeric) / a.calls, $1) "avg_time(s)",+
         |               |          |             | a.query                                                          +
         |               |          |             | from pg_stat_statements a join pg_user b                         +
         |               |          |             | on a.userid = b.usesysid                                         +
         |               |          |             | where b.usename = $2                                             +
         |               |          |             | order by total_time desc                                         +
         |               |          |             | limit $3
 awsuser |           120 |    26417 |    0.004561 | SELECT current_setting($1)
(5 rows)

参考

• PostgreSQL のコマンドラインで SQL を定期的に実行する方法|Everything you do is practice
• Studio ODIN - blog風小ネタ集 > PostgreSQL でも watch