Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 89B6A9F9474 for ; Thu, 1 Nov 2007 18:59:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36078-01-6 for ; Thu, 1 Nov 2007 18:59:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id B961D9F9713 for ; Thu, 1 Nov 2007 18:38:37 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so555752ugf for ; Thu, 01 Nov 2007 14:38:36 -0700 (PDT) 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=Q/wdk8jQ5NtlAgRqI7okZxFyK63l8hXb0p9/U+7Q83A=; b=BRkiIajchnUYpGNDtk6GqfbFJs7ZX28+ZkJDiNtfGjqCfLmIl2X2ho8sKBTgVyCq0mgMRQasLLaBnmmmKtLnANE+LW6A1IPv1/1hv+RCOjctsqWODMDi3ENDrTV/FlnS0nQHGEc1nGivyNHFN80n1xbx2+QmzOTn8YmcLxbnEV4= 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=Fw9PuIA/ylt9Kj1lr+8Zy4ttZgxZfTwlpFaC7EoPq5Wpx35KOuGWPJpJhdc1NFt6pM1a5wdi0Q+N27428GfPXg8IjwCwB4EvpAD54YSWfUXO2uDLIC/E6yJ1ItcmYhmrJ7Ksq6fIYsOQ5kvazJzOxprDlsRrQRFLpj4+UiHN5Ag= Received: by 10.66.240.12 with SMTP id n12mr1504048ugh.1193953115903; Thu, 01 Nov 2007 14:38:35 -0700 (PDT) Received: by 10.67.40.10 with HTTP; Thu, 1 Nov 2007 14:38:35 -0700 (PDT) Message-ID: Date: Thu, 1 Nov 2007 16:38:35 -0500 From: "Scott Marlowe" To: "Carlo Stonebanks" Subject: Re: How to avoid hashjoin and mergejoin Cc: 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: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/24 X-Sequence-Number: 27439 On 11/1/07, Carlo Stonebanks wrote: > I am comparing the same query on two different PG 8.2 servers, one Linux > (8GB RAM) and one Windows (32GB RAM). Both have similar drives and CPU's. > > The Windows posgrestsql.config is pretty well tuned but it looks like > someone had wiped out the Linux config so the default one was re-installed. > All performance-related memory allocation values seem to be set to the > defaults, but mods have been made: max_connections = 100 and shared_buffers > = 32MB. > > The performance for this query is terrible on the Linux server, and good on > the Windows server - presumably because the original Linux PG config has > been lost. This query requires: that "set enable_seqscan to 'off';" Have you run analyze on the server yet? A few general points on performance tuning. With 8.2 you should set shared_buffers to a pretty big chunk of memory on linux, up to 25% or so. That means 32 Meg shared buffers is REAL low for a linux server. Try running anywhere from 512Meg up to 1Gig for starters and see if that helps too. Also turn up work_mem to something like 16 to 32 meg then restart the server after making these changes. Then give us the explain analyze output with all the enable_xxx set to ON. summary: analyze, increase shared_buffers and work_mem, give us explain analyze.