Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 9EC041336FA7 for ; Mon, 22 Nov 2010 07:00:24 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 31804-09 for ; Mon, 22 Nov 2010 11:00:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id 220D91335B36 for ; Mon, 22 Nov 2010 07:00:16 -0400 (AST) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id B7EC115E42AA; Mon, 22 Nov 2010 12:00:15 +0100 (CET) Received: from 89.103.152.5 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Mon, 22 Nov 2010 12:00:15 +0100 Message-ID: <132e294a67bfcf8b3078cac8693c6f48.squirrel@sq.gransy.com> In-Reply-To: References: <3ba1d0462bc7df1c3eb5a6d4777ce08a.squirrel@sq.gransy.com> <22586.1290359761@sss.pgh.pa.us>, Date: Mon, 22 Nov 2010 12:00:15 +0100 Subject: Re: Query Performance SQL Server vs. Postgresql From: tv@fuzzy.cz To: "Humair Mohammed" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/352 X-Sequence-Number: 41376 > > > 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