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

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.


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz