PostgreSQL work_mem: Optimizing Query Performance
The work_mem setting in PostgreSQL controls the amount of memory allocated for internal operations such as sorting and hash joins during query execution. Tuning this parameter can significantly improve performance for resource-intensive queries.
Syntax for Setting work_mem
You can set the work_mem parameter either temporarily for the current session or globally in the configuration file:
1. Set work_mem for a Session:
SET work_mem = '4MB';
2. Update work_mem in postgresql.conf:
work_mem = 4MB
Examples of Using work_mem
Example 1: Adjusting work_mem for a Session
Code:
-- Set work_mem for the current session
SET work_mem = '8MB';
-- Example of a query utilizing work_mem
SELECT *
FROM large_table
ORDER BY column_name;
Explanation:
Example 2: Query Impact with work_mem
Code:
-- Default work_mem (e.g., 4MB)
SET work_mem = '4MB';
EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY column_name;
-- Increased work_mem (e.g., 16MB)
SET work_mem = '16MB';
EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY column_name;
Result:
Example 3: Setting Global work_mem
Code:
-- Update in postgresql.conf
work_mem = 16MB
-- Reload the configuration
SELECT pg_reload_conf();
Note:
Explanation of code Examples:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4