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.

Any ideas?

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.


2 Answers 2


You'll want to use the built-in extension pg_stat_statements, or the external pg_stat_plans enhanced approach.

There aren't any network I/O statistics kept by PostgreSQL yet. There's someone working on it for 9.4, though I don't know if it'll get accepted into 9.4.


I'm sorry for not exactly answering your question but why did you choose row count as a database load testing tool?

Personally I prefer to:

  1. Log all queries with log_min_duration_statement = 0 in postgresql.conf.
  2. Use something like pgbadger to process logs.

After this I have statistical data on:

  1. What queries are lagging behind.
  2. What queries are most common and should be optimized.

This will be some real data, not some assumptions about tuples fetched (while they can be cached or your poison is actually some little query that can be optimized just by growing one index to exempt another sorting pass).

  • Well that's the thing, i am trying to figure out if the value i need (count of select queries) is accessible without having to log everything to the disk as that would during the test period significantly reduce performance for clients with good network connections (in the end all i need is a number, not exactly which statement it is)
    – Chris
    Sep 26, 2013 at 22:56
  • row count? i am just summing up the statistic-values for all objects
    – Chris
    Sep 26, 2013 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.