> > > On Tuesday, April 15, 2014, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Jeff Janes <jeff.ja...@gmail.com> writes: > > On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank <nickeub...@gmail.com> > wrote: > >> Quick followup Jeff: it seems that I can't set work_mem above about 1gb > >> (can't get to 2gb. When I update config, the values just don't change in > >> "SHOW ALL" -- integer constraint?). Is there a work around, or should I > >> tweak something else accordingly? > > > What version are you using? What is the exact line you put in your > config > > file? Did you get any errors when using that config file? Are you sure > > you actually reloaded the server, so that it reread the config file, > rather > > than just changing the file and then not applying the change? > > > I usually set work_mem within a psql connection, in which case you need > to > > quote the setting if you use units: > > set work_mem="3GB"; > > FWIW, it's generally considered a seriously *bad* idea to set work_mem as > high as 1GB in postgresql.conf: you're promising that each query running > on the server can use 1GB per sort or hash step. You probably don't have > the machine resources to honor that promise. (If you do, I'd like to have > your IT budget ;-)) Recommended practice is to keep the global setting > conservatively small, and bump it up locally in your session (with SET) > for individual queries that need the very large value. > > But having said that, Postgres doesn't try to enforce any such practice. > My money is on what Jeff is evidently thinking: you forgot to do "pg_ctl > reload", or else the setting is too large for your platform, in which case > there should have been a complaint in the postmaster log. As noted > elsewhere, the limit for Windows is a hair under 2GB even if it's 64-bit > Windows. > > regards, tom lane Thanks Tom -- quick follow up: I know that 1gb work_mem is a terrible idea for normal postgres users with lots of concurrent users, but for my situations where there will only ever be one connection running one query, why is that a problem on a machine with 16gb of ram. Re:Robert -- thanks for that clarification!
