Speeding up queries without rewriting them

Often, you either can’t or don’t want to rewrite a query. However, you can still try and speed it up through any of the techniques we will discuss here.

How to do it…

By now, we assume that you’ve looked at various problems already, so the following are more advanced ideas for you to try.

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 (TB) of RAM; the previous setting was only used to prove that the query plan is dependent on available memory. Now, issue the following command:

RESET work_mem;

Now, choose a more appropriate...