我正在尝试构建一个Grafana Dashboard,以了解PostgreSQL服务器处理哪些SQL查询。我正在使用pg_stats_statements
扩展。
这是我目前的查询:
SELECT
query,
calls,
FROM pg_stat_statements
ORDER BY calls DESC limit 3;
这让我得到了以下结果:
query | calls
---------+--------
Query 1 | 500000
Query 2 | 250000
Query 3 | 250000
现在,除了calls
之外,我还想选择一个附加值,以查看所有行中每个calls
值与sum(calls)
值的共享情况。这是预期输出:
query | calls | share
---------+--------+------ # 1 000 000 total calls
Query 1 | 500000 | 0.5 # 500 000 / 1 000 000
Query 2 | 250000 | 0.25 # 250 000 / 1 000 000
Query 3 | 250000 | 0.25 # 250 000 / 1 000 000
有可能做到这一点吗?如果有,我如何重写查询以获得此输出?
WITH sum_query AS MATERIALIZED
(select sum(calls) as call_sum from pg_stat_statements)
select
ps.query,
sum(ps.calls),
avg(round((ps.total_time/ps.calls)::numeric,2)) as mean_time,
sum(ps.calls) / (select call_sum from sum_query) as "share"
from pg_stat_statements ps
group by ps.query
在这个查询中,我使用WITH AS MATERIALIZED
来提高性能。