May 2018
Intermediate to advanced
576 pages
30h 25m
English
For queries involving large sorts or for join queries it may be useful to increase the amount of working memory that can be used for query execution. Try setting:
SET work_mem = '1TB';
Then, run EXPLAIN (not EXPLAIN ANALYZE). If the EXPLAIN changes for the query, then it may benefit from more memory. I'm guessing that you don't have access to 1 terabyte of RAM; the previous setting was only used to prove that the query plan is dependent upon available memory. So now, issue the following:
RESET work_mem;
And choose a more appropriate value for production use, such as:
SET work_mem = '128MB';
Remember to increase maintenace_work_mem when creating indexes or adding foreign keys, rather than work_mem.