|
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: Query Performance SQL Server vs. Postgresql > From: tv@xxxxxxxx > To: humairm@xxxxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > > > > > > > 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 > |
