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.
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
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
The default value for
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);