How to quickly identify sessions with high memory usage?

When the PostgreSQL server instance is running, the service load changes, and the system memory usage may change. How to quickly identify sessions with high memory usage when the system memory usage is high?

Redrock Postgres provides a statistics view pg_stat_activity. The memory_used field in the view displays the memory usage of each session. You can run the following query to view the sessions with high memory usage in the instance.

SELECT pg_size_pretty(memory_used), query FROM pg_stat_activity
  ORDER BY memory_used DESC LIMIT 10;

From the above query result, we obtained the SQL statements that are being executed by the sessions. Then, you can run the SQL command EXPLAIN to analyze the execution plan of the query, to further locate the cause of the high memory usage of the SQL statements.