PostgreSQL 11 Administration Cookbook

Increasing work_mem

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 the following:

SET work_mem = '1TB';

Then, run EXPLAIN (not EXPLAIN ANALYZE). If 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 on available memory. Now, issue the following:

RESET work_mem;

Now, choose a more appropriate value for production use, such as the following:

SET work_mem = '128MB';

Remember to increase maintenace_work_mem when creating indexes or adding foreign keys, rather than work_mem