Speeding up queries without rewriting them
Often, youeithercan'tordon't wanttorewrite the query. However, you can still tryandspeed it up through any of the techniques we will discuss here.
How to do it…
By now, we assume that you'velooked at variousproblems already,so the following are more advanced ideas for you totry.
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...