Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 3749F1336B42 for ; Fri, 4 Feb 2011 11:20:23 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 06665-05 for ; Fri, 4 Feb 2011 15:20:17 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 767401337B98 for ; Fri, 4 Feb 2011 11:20:14 -0400 (AST) Received: by bwz15 with SMTP id 15so2816774bwz.19 for ; Fri, 04 Feb 2011 07:20:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:references:user-agent:in-reply-to:mime-version :content-type:subject:from:date:to:cc:message-id; bh=15tuEB+6BtXsSdUXT6m42D9JHZuM4JebRU1Mbdn6a1A=; b=e8VQYGaSAqbzKtX3RpKKef7oavSdQArDUb6J8y9sFDdqAtLX5+yffSPXQH7NKJAvsW aJZNF401Y/MfRl2/DmZR5zZb9n6Vv9Zu6UCLd6UbUgnsMISnALZLQGOGhdVv3I6gaucQ sRB8AqJw1IEol7CGiWmUWwdAVxzvprtHLuxrs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=references:user-agent:in-reply-to:mime-version:content-type:subject :from:date:to:cc:message-id; b=EMooMCtfQ/gYNP7qN3UrW+aVniLYhw3sI2TlDLmcCWwgf9UEKEHWGkgVSIc+zCxlvZ /0VdV7iFtlmIT4pFyZ86giSqAZ5H9MSXpvVcx8X58zNggbuTEiH8gGvJWUaAhWKepvp2 fBZgPfQalNFca/k8vwRJYLkKmaKtx5m4xb+cI= Received: by 10.204.76.1 with SMTP id a1mr10620460bkk.5.1296832813289; Fri, 04 Feb 2011 07:20:13 -0800 (PST) Received: from [212.15.176.116] ([212.15.176.116]) by mx.google.com with ESMTPS id f20sm483204bkf.16.2011.02.04.07.20.10 (version=SSLv3 cipher=RC4-MD5); Fri, 04 Feb 2011 07:20:12 -0800 (PST) References: <4D4C10EA.2080901@2ndquadrant.com> User-Agent: K-9 Mail for Android In-Reply-To: <4D4C10EA.2080901@2ndquadrant.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----DG27KL5KCCOOMG0UH6IBGFD4TDXN1H" Subject: Re: Query performance with disabled hashjoin and mergejoin From: Ivan Voras Date: Fri, 04 Feb 2011 16:13:19 +0100 To: Greg Smith ,Ivan Voras CC: pgsql-performance@postgresql.org Message-ID: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201102/268 X-Sequence-Number: 42406 ------DG27KL5KCCOOMG0UH6IBGFD4TDXN1H Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Sorry for the misunderstaning: of course not default "normal" settings; shared buffers, work mem, wal segments and others have been tuned according to available hardware (e.g. 4 GB, 32 MB, 10 for these settings, respectively). I meant "planner default settings" in the post. -- Sent from my Android phone, please excuse my brevity. Greg Smith wrote: Ivan Voras wrote: > The "vanilla" plan, with default settings is: Pause here for a second: why default settings? A default PostgreSQL configuration is suitable for systems with about 128MB of RAM. Since you say you have "good enough hardware", I'm assuming you have a bit more than that. The first things to try here are the list at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; your bad query here looks like it might benefit from a large increase to effective_cache_size, and possibly an increase to work_mem as well. Your "bad" plan here is doing a lot of sequential scans instead of indexed lookups, which makes me wonder if the change in join types you're forcing isn't fixing that part as a coincidence. Note that the estimated number of rows coming out of each form of plan is off by a factor of about 200X, so it's not that the other plan type is better estimating anything. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Serv ices, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books ------DG27KL5KCCOOMG0UH6IBGFD4TDXN1H Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Sorry for the misunderstaning: of course not default "normal" settings; shared buffers, work mem, wal segments and others have been tuned according to available hardware (e.g. 4 GB, 32 MB, 10 for these settings, respectively). I meant "planner default settings" in the post.
--
Sent from my Android phone, please excuse my brevity.

Greg Smith <greg@2ndquadrant.com> wrote:
Ivan Voras wrote: > The "vanilla" plan, with default settings is: Pause here for a second: why default settings? A default PostgreSQL configuration is suitable for systems with about 128MB of RAM. Since you say you have "good enough hardware", I'm assuming you have a bit more than that. The first things to try here are the list at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; your bad query here looks like it might benefit from a large increase to effective_cache_size, and possibly an increase to work_mem as well. Your "bad" plan here is doing a lot of sequential scans instead of indexed lookups, which makes me wonder if the change in join types you're forcing isn't fixing that part as a coincidence. Note that the estimated number of rows coming out of each form of plan is off by a factor of about 200X, so it's not that the other plan type is better estimating anything. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
------DG27KL5KCCOOMG0UH6IBGFD4TDXN1H--