Speeding up queries without rewriting them

Often, you either can't or don't want to rewrite the query. However, you can still try and speed it up through any of the techniques discussed 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:

SET work_mem = '1TB';

and 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 used only 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...