哪一个更准确:以下两者之一?
select numbackends from pg_stat_database;
select count(*) from pg_stat_activity;
select numbackends from pg_stat_database;
select count(*) from pg_stat_activity;
这两个要求并不等价。第一个要求的等价版本应该是:
SELECT sum(numbackends) FROM pg_stat_database;
在这种情况下,我会期望第一个版本要比第二个版本稍微快一些,因为它需要计算的行数更少。但你不太可能测量出差异。
两个查询都基于完全相同的数据,所以它们的准确度是相等的。SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null;
SELECT count(*) FROM pg_stat_activity WHERE datname is not null;
- blindguy以下查询非常有帮助
select * from
(select count(*) used from pg_stat_activity) q1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;
它们肯定可能会给出不同的结果。更好的一个是
select count(*) from pg_stat_activity;
这是因为它包括了与WAL发送进程的连接,这些连接被视为常规连接,并计入max_connections
限制。
按其状态汇总所有Postgres会话(多少个处于空闲状态,多少个正在执行某些操作...)
select state, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1 order by 1;