On Sun, Nov 21, 2010 at 10:21 PM, Humair Mohammed <huma...@hotmail.com>wrote:
> > Correct, the optimizer did not take the settings with the pg_ctl reload > command. I did a pg_ctl restart and work_mem now displays the updated value. > I had to bump up all the way to 2047 MB to get the response below (with > work_mem at 1024 MB I see 7 seconds response time) and with 2047 MB (which > is the max value that can be set for work_mem - anything more than that > results in a FATAL error because of the limit) the results are below. The > batches and memory usage are reflecting the right behavior with these > settings. Thanks for everyones input, the result is now matching what SQL > Server was producing. > > I believe you can set work_mem to a different value just for the duration of a single query, so you needn't have work_mem set so high if for every query on the system. A single query may well use a multiple of work_mem, so you really probably don't want it that high all the time unless all of your queries are structured similarly. Just set work_mem='2047MB'; query; reset all; But you should wait until someone more knowledgable than I confirm what I just wrote.
