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:

• Viewing current PostgreSQL queries
• pg_stat_get_backend_activity