Re: Workaround for working_mem max value in windows?

["Martin French" <Martin.French@xxxxxxxxxxxxx>]

On 16 Apr 2014, at 17:35, "Nick Eubank" <nickeubank@xxxxxxxxx> wrote:

On Wed, Apr 16, 2014 at 1:29 AM, amulsul <sul_amul@xxxxxxxxxxx> wrote:

>Anyone found a work around?

Wouldn't it helpful, setting it in your session?

set work_mem='2000MB';
set maintenance_work_mem='2000MB';

do rest of sql after .....

Regards,
Amul Sul



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Workaround-for-working-mem-max-value-in-windows-tp5800170p5800216.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Thanks all!

Sorry Martin, should have been clearer on my usage plans: I'm only interested in optimizing for single-connection, sequential high-demand queries, so I think I'm safe bumping up memory usage, even if it's usually a disastrous idea for most users.  I'll definitely check with the Enterprise folks!

Amul: thanks for the followup! Unfortunately, setting locally faces the same limitation as setting things in the config file -- I get an "ERROR: 3072000 is outside the valid range for parameter "work_mem" (64 .. 2097151)

SQL state: 22023" problem if I set above ~1.9gb. :(

Nick, the issue would still remain if you set work_mem to 2Gb and joined 8 tables together, it would consume too much memory. 

So if you DO decide to proceed with this, I would recommend to err on the side of caution and be a little more concerned with tuning your SQL statements

Good luck. 

============================================= 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@xxxxxxxxxxxxx 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. =================================================