I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics but nothing specifically reflecting queries.
Why do I need it?
I am developing a client application and I want to find out if I missed any sections in my program where a lot of small queries are executed in bulk that could possibly be done in one big query to speed up queries from very far away clients (signal propagation delay).
To monitor this I wrote myself a tool displaying a "count per second" graph by polling
SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database SELECT sum(tup_fetched) FROM pg_stat_database
and displaying the difference to the last poll.
However the first statement does not seem to update propery, or not display "select" statements at all as it is sometimes showing 0, which is impossible because it should at least show 1 even on an idle server due to the poll-query.
The second query does not seem to work either, it is showing 0 most of the time.
Also if anyone knows if there is a way to get the actual network read/write statitic that would help as well. (not in packages or traffic, but in "recieved: query, sent: 1000 rows, received: next read request, sent: (last) 20 rows -> 2 operations recorded", so basically I can derive with a ping of 150ms the total time needed would be 600ms minimum, which could be reduced to 300ms by increasing the fetch size)
If you have any ideas as to how to test this with a different approach I am also open to suggestions.