Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4BB5F2E00B4 for ; Thu, 21 Feb 2008 19:16:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 97418-08 for ; Thu, 21 Feb 2008 19:16:12 -0400 (AST) Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.176]) by postgresql.org (Postfix) with ESMTP id 0ACB92E00BD for ; Thu, 21 Feb 2008 19:16:11 -0400 (AST) Received: by el-out-1112.google.com with SMTP id r27so235197ele.5 for ; Thu, 21 Feb 2008 15:16:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; 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=l/ciAhDZGJGXmIu+qo0pCN48aAMpAJIH+Eq53QYZlx0=; b=mhBXhZG/SU6/ZIejQlCeXTerfMUMkA/XO93olLYgDKgTJiC9uK6tq9e8q8WrDNM2XymA9hs+stOCdGEFenzhcc0JWsYJB3+4JPr02PQmYfIShUP3YRutnDz4fOmD7kjZVoVoNKFKIoTR8bzNymNb3Ca8UsxwylNNRzWKD5g+J6U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=jN7MqW2Tgn+JN7Fpp2g4X3PZeWSsa3xKRuhlOhQwfVk8S1HQi0hEMuAcpNFwE3k8o8b/1j6Dfd/sM+MJr9iD6Wrh/fnw3zldnEEI7uKWvYCM0MFe93iFnMr6LUbxbolbV+n7+j1OiHatJWR+Ee9J8Mk5Ncd+OFg8tVXs51rN8iw= Received: by 10.114.155.1 with SMTP id c1mr831723wae.105.1203635770318; Thu, 21 Feb 2008 15:16:10 -0800 (PST) Received: by 10.114.133.6 with HTTP; Thu, 21 Feb 2008 15:16:10 -0800 (PST) Message-ID: Date: Thu, 21 Feb 2008 17:16:10 -0600 From: "Scott Marlowe" To: "Mark Kirkwood" Subject: Re: 4s query want to run faster Cc: "Adonias Malosso" , "Claus Guttesen" , pgsql-performance@postgresql.org In-Reply-To: <47BE023A.6000905@paradise.net.nz> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com> <8a5d3c890802211311m5fab66d6q865fda1737e47802@mail.gmail.com> <8a5d3c890802211323j2a7e2ebdye42f988222046e44@mail.gmail.com> <47BE023A.6000905@paradise.net.nz> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/279 X-Sequence-Number: 28821 On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood wrote: > The other parameter you might want to look at is effective_cache_size - > increasing it will encourage index use. On a machine with 16GB the > default is probably too small (there are various recommendations about > how to set this ISTR either Scott M or Greg Smith had a page somewhere > that covered this quite well - guys?). > > Obviously, decreasing random_page_cost fixed this query for you, but if > find yourself needing to tweak it again for other queries, then look at > changing effective_cache_size. effective_cache_size is pretty easy to set, and it's not real sensitive to small changes, so guesstimation is fine where it's concerned. Basically, let your machine run for a while, then add the cache and buffer your unix kernel has altogether (top and free will tell you these things). If you're running other apps on the server, make a SWAG (scientific wild assed guess) how much the other apps are pounding on the kernel cache / buffer and set effective_cache_size to how much you think postgresql is using of the total and set it to that. If your data set fits into memory, then setting random page cost closer to 1 makes a lot of sense, and the larger effective cache size.