PostgreSQL: Improve the performance of Query Sort operation by setting work_mem

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Yesterday, I was doing performance tuning for one our long running queries. I have checked the execution plan of the query and found that query sort operations are spilling to disk instead of fitting in memory.

Every time, just tuning the Table Indexes should not be the ultimate solution to improve the query performance.

There are always N factors behind the bad performance shape of your query.

How can you know that your operations are spilling to disk?

When you execute EXPLAIN ANALYZE for your query and if you see a line like Sort Method: external merge Disk: 88342kB, your operations are spilling to disk.

Is spilling to disk a bad operation?

Yes: Because it requires more CPU/IO for the disk operation and it leads to biggest performance cause.

What is the solution?

The simple solution is, validate the configuration of a work_mem parameter.
If you do a lot of complex sorts and have a lot of memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents.

How can you configure work_mem?

A work_mem of at least 16MB would keep the intermediate data in memory and likely improve the query response time.
You can set at the server level for the general use (in postgresql.conf).

You can also set at your session level using below sample query. Increase the setting until you see Memory instead of Disk in the EXPLAIN output.