Increase work_mem parameter in PostgreSQL to speed up expensive queries

Sorting, joins, aggregations and the use of subqueries in IN operator in PostgreSQL may require a lot of memory for internal sorting operations and hash tables. When PostgreSQL requires more memory to perform one of those operations than work_mem parameter allows, it uses temp files on disk. That may be significantly slower than performing identical operations in memory.

Lines like Sort Method: external merge Disk: <XXXX>kB show up in EXPLAIN ANALYZE output for an expensive query if that happens. work_mem may be increased to make such queries faster. It should be set to something higher than those <XXXX>kB. Check EXPLAIN ANALYZE output for the query with increased work_mem to pick a value that makes the ...external merge Disk... message disappear. The value may sometimes be almost double of those <XXXX>kB.

The default value for work_mem is 4MB. work_mem can be set on the database server level, for an individual session and even for an individual transaction. That allows to fine tune memory usage only for expensive queries without affecting the users and connections that perform inexpensive queries.

It is important to remember that PostgreSQL uses up to work_mem of memory for each sorting operation or hash table in a query. In other words, if a query has four operations like that, PostgreSQL may use four times work_mem of memory.

This example shows how to allow a specific expensive query to use up to 256 MB of real memory to perform sorting and then resets work_mem for this session to the default value for the server.

SET work_mem = '256MB';  
select * from users order by lower(display_name);  
RESET work_mem;  

This example sets work_mem for a single transaction and then resets it to the default value for the server.

SET LOCAL work_mem = '256MB';  
explain analyze select * from users order by lower(display_name);