Listing running queries in PostgreSQL

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 [1] 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.

References

[1] http://chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s