Tuning Postgres for Single connection use

>
>
> On Tuesday, April 15, 2014, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> > On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank <nickeubank(at)gmail(dot)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!