PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Setting work_mem to the value of external merge Disk

I have large query that need’s to be optimized. Upon generating explain plan using EXPLAIN(ANALYZE) I noticed that the query is using the disk space in sorting.

Sort Method: external merge Disk: 201,816kB
Worker 0: Sort Method: external merge Disk: 205,392kB
Worker 1: Sort Method: external merge Disk: 200,776kB

Is it safe If I temporarily set the work_mem to 200mb or should I just remain in 4mb?

Generate query plan and sort uses disk instead of ram.

Tags:

2

Answers


  1. 0

    If it is safe or not depends only on whether you have enough memory or not. I would use a database transaction and change the parameter with SET LOCAL, so that it gets reset automatically when the transaction ends.

    Login or Signup to reply.
  2. 0

    It should be safe if you have that much memory available to be used. But what it probably won’t be is effective. Data written to disk is serialized into a compact form but while live in memory it is not, so if it took 200MB of temp disk space, it could easily take 600MB of work_mem to do the same sort in memory.

    The default setting of 4MB is for the benefit of tiny servers. Any serious server dedicated to the use of PostgreSQL should have that changed to a larger value anyway (but likely not 600MB large).

    Also, you likely won’t see that much improvement on switching to an in-memory sort. In old versions, there used to be a big improvement in memory over disk, but with recent optimizations that is no longer the case generally in my experience.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search