I did some further analysis and here are the results:
work_mem;response_time
1MB;62 seconds
2MB;2 seconds
4MB;700 milliseconds
8MB;550 milliseconds
In all cases shared_buffers were set to the default value of 32MB. As you can see the 1 to 2 MB jump on the work_mem does wonders. I probably don't need this to be any higher than 8 or 16 MB. Thanks to all for help!
Humair
> Date: Mon, 22 Nov 2010 12:00:15 +0100> Subject: Re: [PERFORM] Query Performance SQL Server vs. Postgresql> From: tv@fuzzy.cz> To: humairm@hotmail.com> CC: pgsql-performance@postgresql.org> > >> >> > 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.> > Hm, can you post explain plan for the case work_mem=1024MB. I guess the> difference is due to caching. According to the explain analyze, there are> just cache hits, no reads.> > Anyway the hash join uses only about 40MB of memory, so 1024MB should be> perfectly fine and the explain plan should be exactly the same as with> work_mem=2047MB. And the row estimates seem quite precise, so I don't> think there's some severe overestimation.> > Tomas>