work_mem v17
Parameter type: Integer
Default value: 1MB
Range: 64kB to 2097151kB
Minimum scope of effect: Per session
When value changes take effect: Immediate
Required authorization to activate: Session user
Specifies the amount of memory for internal sort operations and hash tables to use before writing to temporary disk files.
For a complex query, several sort or hash operations might run in parallel. Each operation is allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions might perform such operations concurrently. Therefore, the total memory used might be many times the value of work_mem
. Keep this information in mind when choosing the value.
Sort operations are used for ORDER BY
, DISTINCT
, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN
subqueries.
Reasonable values are typically between 4MB and 64MB, depending on the size of your machine, how many concurrent connections you expect (determined by max_connections
), and the complexity of your queries.