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
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
EXPLAIN ANALYZE output for an expensive query if that happens.
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);