Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id F00D16339B7 for ; Wed, 4 Mar 2009 05:18:23 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34540-09 for ; Wed, 4 Mar 2009 05:18:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.hi-media-techno.com (smtp1.hi-media-techno.com [195.158.241.85]) by mail.postgresql.org (Postfix) with ESMTP id 2C5A76323E3 for ; Wed, 4 Mar 2009 05:18:19 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by smtp1.hi-media-techno.com (Postfix) with ESMTP id E326293D40; Wed, 4 Mar 2009 10:18:17 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at smtp1.backbone.hi-media-techno.com Received: from smtp1.hi-media-techno.com ([127.0.0.1]) by localhost (smtp1.backbone.hi-media-techno.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id te1KQMv2teCE; Wed, 4 Mar 2009 10:18:13 +0100 (CET) Received: from mail1.hi-media-techno.com (unknown [192.168.27.12]) by smtp1.hi-media-techno.com (Postfix) with ESMTP id 4AF0593BB0; Wed, 4 Mar 2009 10:18:13 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by mail1.hi-media-techno.com (Postfix) with ESMTP id 456A76D8588; Wed, 4 Mar 2009 10:18:13 +0100 (CET) X-Virus-Scanned: amavisd-new at Received: from mail1.hi-media-techno.com ([127.0.0.1]) by localhost (mail1.hi-media-techno.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id M+BGeDSIUhdL; Wed, 4 Mar 2009 10:18:13 +0100 (CET) Received: from dim.localnet (unknown [83.167.62.196]) by mail1.hi-media-techno.com (Postfix) with ESMTP id 0E0E26D8589; Wed, 4 Mar 2009 10:18:13 +0100 (CET) From: Dimitri Fontaine Organization: Hi-Media To: pgsql-performance@postgresql.org Subject: Re: work_mem in high transaction rate database Date: Wed, 4 Mar 2009 10:16:07 +0100 User-Agent: KMail/1.11.0 (Linux/2.6.26-1-686; KDE/4.2.0; i686; ; ) Cc: Scott Marlowe , Flavio Henrique Araque Gurgel References: <29144661.96951236126415852.JavaMail.root@mail.4linux.com.br> <30792576.96971236126536868.JavaMail.root@mail.4linux.com.br> In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart8787068.HPSRusOfh6"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200903041016.09765.dfontaine@hi-media.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/41 X-Sequence-Number: 32963 --nextPart8787068.HPSRusOfh6 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, On Wednesday 04 March 2009 02:37:42 Scott Marlowe wrote: > If some oddball query really needs a lot of work_mem, > and benchmarks show something larger work_mem helps, consider raising > the work_mem setting for that one query to something under 1G (way > under 1G) That makes it noticeably faster. =A0Don't allocate more than a > test shows you helps. The probably easiest way to integrate this into an existing application is= =20 this way, in my experience: BEGIN; SET LOCAL work_mem TO '650MB'; SELECT -- the query requiring such a large setting COMMIT; Right after the commit the global configured work_mem (or the previous=20 session's one, in fact) will be in effect, you won't have to reset it yours= elf. Regards, =2D-=20 dim --nextPart8787068.HPSRusOfh6 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEABECAAYFAkmuRtcACgkQlBXRlnbh1bnkegCgiHfS/kliSBevBotRmaYnY9bO zkMAnidr2LL5i9+u4gdO1tXoniYgXg4o =urWC -----END PGP SIGNATURE----- --nextPart8787068.HPSRusOfh6--