> Hi all, > > A few years ago someone said postgres windows can't set working_mem > above about 2 GB (www.postgresql.org/message-id/17895.1315869622@sss.pgh.pa.us > -- seems to be same for maintenance_working_mem ). Im finding > limit still present.
Setting work_mem higher than 2GB on a 16GB machine could easily run the server out of memory.
work_mem is set on a "per client" and "per sort" basis, so setting it to 2GB would exhaust the amount of available ram very quickly on complex queries with multiple sorts, (or with a number of clients greater than 8 - although you mention that you're using a single user; that doesn't mean that there is only 1 connection to the database).
The same rule applies with maintenance_work_mem, more than 1 autovacuum would use n multiples of maintenance_work_mem, again exhausting the server very quickly.
> > I'm doing single user, single connection data intensive queries and > would like to set a higher value on windows to better use 16gb built > in ram (don't control platform, so can't jump to Linux). > > Anyone found a work around? >
PostgreSQL on windows is maintained by EnterpriseDB IIRC, so maybe someone on their forums has any ideas on this, as I doubt very much that the extra work in the PostgreSQL core would be undertaken give the comment by Tom in the thread you posted.
Romax Technology Limited A limited company registered in England and Wales. Registered office: Rutherford House Nottingham Science and Technology Park Nottingham NG7 2PZ England Registration Number: 2345696 VAT Number: 526 246 746
Telephone numbers: +44 (0)115 951 88 00 (main)
For other office locations see: http://www.romaxtech.com/Contact ================================= =============== E-mail: info@romaxtech.com Website: www.romaxtech.com =================================
================ Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =================================================