Posgresql Optimization technique: work_mem

What is work_men?

This parameter determines how much memory is allocated to in-memory sort operation. If high values is set, then the query will execute very fast compared with
using disk-based sort. By default it is set to be 1 MB. It is a parameter of per-sort rather than per-client.

In case a query containing more than one sort operations, the actual memory required will be work_mem times the number of
sort operations. It is difficult to predict how many sort operations are needed per client.

One way to calculate suitable value is to see how much free RAM is around after shared_buffers is allocated, divide by max_connections, and then take a part of that figure and then divided by two. Sort operations include ORDER BY, DISTINCT, GROUP BY, UNION AND merge joins.

Assuming you have a postgresql Database and you want to perform an in-memory sort operation, you can set work-men to 2 MB.

SET work_mem = '2MB';

This allocates more memory for the sort operation, making the query execute faster.