It can be very useful to see what queries the database server is currently running. I found it’s possible using the following query:
SELECT datname, procpid, current_query FROM pg_stat_activity;
On PostgreSQL 8.4.2 this query just worked. According to reference  below, you need to add a config option to the
postgresql.conf file, but that was actually detrimental to the database starting up from a fresh boot, even though reloading the database using
pg_ctl reload as the article suggests didn’t show any errors.
I have found that if you run the query above twice in a row and you see the same query listed, that’s a huge bottleneck. Perusing that particular query usually leads to creating an index, which speeds performance up significantly.