Monitoring and Diagnosis - PostgreSQL 9 Administration - page 189

There's more...
Sometimes you don't care about getting all queries currently running, but are just interested in
seeing some of these. Or you may not like to connect to database just to see what is running.
How to catch queries which only run for a few milliseconds
As most queries on modern OLTP (Online Transaction Processing) systems take only a
few milliseconds to run, it is often hard to catch those when simply probing the pg_stat_
activity table.
To see them actually executing you'd have to slow them down. We don't want to do that!
In PostgreSQL 9.0, there is a contrib module called pg_stat_statements that captures query
execution statistics in real time. See the documentation at the following URL:
http://www.postgresql.org/docs/9.0/interactive/pgstatstatements.html
In both cases, you can script the queries and use select pg_sleep(5) to get the <wait>
happen automatically. You can get subsecond waits by using floating numbers, such as pg_
sleep(0.5) for a half second delay.
To collect the queries you just forced to be logged you can start tail -f /var/log/
postgresql/postgresql-9.0-main.log > account_queries.log in one window,
then run the preceding lock trick in another and then just use Ctrl-C to kill the tail -f process.
Now you have a much smaller log in account_queries.log.
How to watch longest queries
Another thing of interest for which you may want to look is long-running queries. To get a list of
running queries ordered by how long they have been executing, use the following:
select
current_timestamp - query_start as runtime,
datname,
usename,
current_query
from pg_stat_activity
where current_query != '<IDLE>'
order by 1 desc;
This will return currently running queries ordered by how long they have been running, with
the longest ones in front (the first field: order by 1 desc ). On busy systems, you may want to
limit the set of queries returned to only the first few ones (add LIMIT 10 to the end), or only
to queries which have been running over a certain time (for queries which have been running
over one minute add ' current_timestamp - query_start 1 ' min to the WHERE clause).