On Sun, Nov 21, 2010 at 10:21 PM, Humair Mohammed
<humairm@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.