Which of the following two is more accurate?
select numbackends from pg_stat_database;
select count(*) from pg_stat_activity;
-
8Better in terms of what?Jason Towne– Jason Towne2011-03-11 00:45:08 +00:00Commented Mar 11, 2011 at 0:45
Add a comment
|
6 Answers
Those two queries aren't equivalent. The equivalent version of the first one would be:
SELECT sum(numbackends) FROM pg_stat_database;
In that case, I would expect that version to be slightly faster than the second one, simply because it has fewer rows to count. But you are not likely going to be able to measure a difference.
Both queries are based on exactly the same data, so they will be equally accurate.
3 Comments
gargii
Not true they are equally accurate. See my answer.
neonidian
Note that when the postgres command line tool, PSQL is used for executing this query, the total number of connections is the result of this query - 1 since the psql connection made is also included as a connection
blindguy
These two functions may pull from the same data set but do not always return the same values as you have written them. When I query there are null data base connections that are counted different in each. This works to get the same answer
SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null; SELECT count(*) FROM pg_stat_activity WHERE datname is not null;The following query is very helpful
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;
3 Comments
bet
can you explain what the 'used' and 'res_for_super' columns?
tbo
Hello, used are used connection, res_for_super is connections reserved for superuser access
vadimbog
This one is good as you can see at a glance how many are used at the moment, how many are reserved for superuser connection (i.e. think of a scenario when all the connections are consumed intentionally or not, then there are 3 left for admin to connect and kill some unnecessary/problematic ones), and the max possible number of connections. (as per the config).
They definitely may give different results. The better one is
select count(*) from pg_stat_activity;
It's because it includes connections to WAL sender processes which are treated as regular connections and count towards max_connections.
See max_wal_senders
1 Comment
Nux
Do they thought? I had an overflow on max_connections and
SELECT sum(numbackends) FROM pg_stat_database was exactly the number I had in max_connections. And in pg_stat_activity there are always a few more (I have 5 more)Aggregation of all postgres sessions per their status (how many are idle, how many doing something...)
select state, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1 order by 1;
Comments
pg_stat_activity doesn't give you the real connections, it gives you the back ends. Parallel executions would have multiple entries, so your select should be like:
select count(*) from pg_stat_activity where leader_pid is not null;
Comments
From looking at the source code, it seems like the pg_stat_database query gives you the number of connections to the current database for all users. On the other hand, the pg_stat_activity query gives the number of connections to the current database for the querying user only.
2 Comments
Magnus Hagander
That's incorrect. pg_stat_activity gives all connections as well, regardless of user. It then gives you a field which says which user it is, that you can filter on if you want to. It will not give you the text of the query if you're not either the same user or a superuser, but it will still show the connection.
Brian L
You're right. I didn't look closely enough at the view definition. The restriction on userid is just for the join against pg_authid. My mistake.