KB483745: How to increase the work_mem setting in your PostgreSQL database

In a PostgreSQL database, there is a setting called

work_mem

work_mem

 work_mem

Prerequisite

Before proceeding, first check the execution plan of the query to identify if the

work_mem

1. In Strategy DB (Database) Query tool, execute the following command:
    

   Explain analyze %detail query%.

   
   
2. If the parameter

   Sort Method: external merge  Disk: 7904kB 

   is in the output, it means you should enlarge the

   work_mem

   setting to increase performance.
   For example:
   

   Unique  (cost=1776305.27..1795533.74 rows=1281898 width=125) (actual time=5724428.491..5724482.724 rows=53884 loops=1)	 ->  Sort  (cost=1776305.27..1779510.01 rows=1281898 width=125) (actual time=5724418.229..5724440.259 rows=53884 loops=1)	       Sort Key: info.object_id, info.object_type, info.parent_id, info.object_name, info.subtype	       Sort Method: external merge  Disk: 7904kB	       ->  Merge Anti Join  (cost=1.11..1479755.68 rows=1281898 width=125) (actual time=81.503..5716910.713 rows=53884 loops=1)	             Merge Cond: (info.object_id = depn.depn_objid)	             Join Filter: ((info.project_id = depn.depn_prjid) OR (info.project_id = '38A062302D4411D28E71006008960167'::bpchar))	             ->  Index Scan using pk_objinfo on dssmdobjinfo info  (cost=0.55..195982.82 rows=1298902 width=125) (actual time=32.483..1055089.072 rows=1298855 loops=1)	                   Index Cond: (project_id = '6687D3624ADF1788083B618D23BBFE33'::bpchar)	                   Filter: (object_state = 1)	                   Rows Removed by Filter: 1018	             ->  Index Only Scan using ix_objdepn_depn on dssmdobjdepn depn  (cost=0.56..1144184.24 rows=7799180 width=66) (actual time=0.066..4654472.770 rows=7799152 loops=1)	                   Heap Fetches: 7799159	Planning Time: 195.356 ms	Execution Time: 5724504.323 ms

   
   

How to Increase the work_mem Setting

There are a few ways to increase the

work_mem

work_mem

Set the work_mem setting at the database server level

1. Open the PostgreSQL database config file located at

   %PGSERVER%/Data/postgresql.conf.

2. Locate the line

   #work_mem = 4MB # min 64kB.

3. Modify the value to 

   work_mem = 50MB # min 64kB

4. Restart the database server.

 The change will take affect and apply to all user sessions.
 

Set the work_mem value for each user

In the PostgreSQL database, execute the following command to set the

work_mem

ALTER ROLE usernameA SET work_mem TO '1GB'

Set the work_mem value in the DSN

For a Windows DSN

• Open the ODBC PostgreSQL Wire Protocol Driver Setup window.
• Click the Advanced tab.
• In the Initialization String field, enter SET work_mem=51200.
  

• Click Apply.
• Click OK.

This change will apply to all connections created by the DSN.
 

For a Linux DSN

1. Open the odbc.ini file.
2. Add the following parameter to the file:
   For a native Postgre driver:
   

   ConnSettings=SET WORK_MEM=51200

   
   For a progress Postgre driver:
   

   InitializationString=SET WORK_MEM=51200

   
   

This change will apply to all connections created by the DSN.