Re: Query Performance SQL Server vs. Postgresql

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