PostgreSQL – Check Running SQLs

Recently, there is a performance issue on the application which i am supporting and i find that the bottleneck is related to the SQL query speed. Therefore, my SA suggests me to add an index on the table to increase the query speed.

But before i could add the index, i have to find out the SQL which runs slow. In PostgreSQL, you can query the recent SQLs by the pg_stat_get_backend_activity() function. Here shows you an example.

1. Set stats_command_string = true in postgresql.conf

2. Restart PostgreSQL server

3. Run the following SQL to get the recent queries

SELECT
	backendid,
	pg_stat_get_backend_pid(S.backendid) AS procpid,
	pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
	(SELECT pg_stat_get_backend_idset() AS backendid) S;

 

Done =)

Reference:

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.