Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E4AF09F96CB for ; Fri, 9 Nov 2007 15:13:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 48788-08 for ; Fri, 9 Nov 2007 15:13:13 -0400 (AST) Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.238]) by postgresql.org (Postfix) with ESMTP id 4B7819FCB9C for ; Fri, 9 Nov 2007 15:12:45 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 68so391442wri for ; Fri, 09 Nov 2007 11:12:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=O1Xfy18tHJ6gy+aLHFDK5u2EQnkZdKVAJfAN9JWfOow=; b=IBJncXRxr+szJQj9Ai5cZ1Kj2paRt1zN54HCgv/TrlQz6yaLvLLwpeEJpo4MFZdI+k6tL32TVFZPDIINyq72HAakbzoNUK3pLud7OpvBabjn06EkgVPv+kpXpY9Owy2XZ/kV2loB1+ZWlkIpzQsfdM99HdBqJFzArsEMwUk8Vss= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ctepr3JQUTxgIAIEs06evIhKVGc4pS11r6TB65kgKBooK3glxLgvkpnqOFndhMQKxwSdXuSO629SQCOBLjrPFRzh8cc4RefgPUQDW8XTkilReRdVff14s/dM6oEkPVqXUlWjZ/qdgT17H4f8V71+OikmYP6HGfWOVGpeaDJzp+4= Received: by 10.67.22.2 with SMTP id z2mr850032ugi.1194635562389; Fri, 09 Nov 2007 11:12:42 -0800 (PST) Received: by 10.67.95.18 with HTTP; Fri, 9 Nov 2007 11:12:42 -0800 (PST) Message-ID: Date: Fri, 9 Nov 2007 13:12:42 -0600 From: "Scott Marlowe" To: "Campbell, Lance" Subject: Re: work_mem and shared_buffers Cc: "Heikki Linnakangas" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <47349F4E.5070204@enterprisedb.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/152 X-Sequence-Number: 27567 On Nov 9, 2007 12:08 PM, Campbell, Lance wrote: > How do you know when you should up the value of work_mem? Just play > with the number. Is there a query I could do that would tell me if > PostgreSql is performing SQL that could use more memory for sorting? Trial and error. Note that you can set work_mem for a given session. While it may seem that making work_mem bigger will always help, that's not necessarily the case. Using this query: select count(*) from (select * from myreporttable where lasttime > now() - interval '1 week' order by random() ) as l I did the following: (I ran the query by itself once to fill the buffers / cache of the machine with the data) work_mem Time: 1000kB 29215.563 ms 4000kB 20612.489 ms 8000kB 18408.087 ms 16000kB 16893.964 ms 32000kB 17681.221 ms 64000kB 22439.988 ms 125MB 23398.891 ms 250MB 25461.797 ms Note that my best time was at around 16 Meg work_mem. This data set is MUCH bigger than 16 Meg, it's around 300-400 Meg. But work_mem optimized out at 16 Meg. Btw, I tried it going as high as 768 Meg, and it was still slower than 16M. This machine has 2 Gigs ram and is optimized for IO not CPU performance.