Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 907E364FC27 for ; Tue, 23 Sep 2008 00:25:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10314-09 for ; Tue, 23 Sep 2008 00:25:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.157]) by postgresql.org (Postfix) with ESMTP id AF45864FC26 for ; Tue, 23 Sep 2008 00:25:04 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so1715926fgg.23 for ; Mon, 22 Sep 2008 20:25:03 -0700 (PDT) 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=r62WBFrK7L3CMNEN+EILhcUb/eHx/C1Snsot7LMCkMw=; b=dFZNopfzuQRD44cobV7/9KLHbCoSF9q/j9McR7KNBDAFwKtHuCYmklvB+iSCiQiKaU WwAOoo3Hvpg8NEf9F4QM4hqxzqgwZWgwl1l/ItXCSwhfs/c2PXqqNpLN7hfoD8FIcd+X YLLFJLyRBMjB8ArUXzI7u+dd6pB6gZ2VBFkPo= 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=f3WX2mXipwPUeqMjFi0XTozGuAdC5Bc23d/IQOUGZzjmn/SEiSpSWw1mUDwlxXjX7K vNsgqRmYU7P/1rWK4tPjg5z0Nc+bMZsKOimE1ZjOSLsWJolL7sB5chjNVD1IBL3Cr+8w wv4U+PJB6Vd5sybXAzavvIpEfMxMCTQD78kBE= Received: by 10.187.160.1 with SMTP id m1mr647856fao.72.1222140303238; Mon, 22 Sep 2008 20:25:03 -0700 (PDT) Received: by 10.187.225.11 with HTTP; Mon, 22 Sep 2008 20:25:03 -0700 (PDT) Message-ID: <34d269d40809222025k2b2252dft89db471649c8eef8@mail.gmail.com> Date: Mon, 22 Sep 2008 21:25:03 -0600 From: "Alex Hunsaker" To: "Tom Lane" Subject: Re: hash index improving v3 Cc: "Kenneth Marshall" , "Xiao Meng" , "Zdenek Kotala" , pgsql-patches@postgresql.org, "Jonah H. Harris" In-Reply-To: <7246.1221445012@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <34d269d40809052249h59750f83v4b19a50d0555580@mail.gmail.com> <20080909134839.GC6714@it.is.rice.edu> <34d269d40809091823t204e252fl246443be71aed9ff@mail.gmail.com> <20080910130401.GG6714@it.is.rice.edu> <34d269d40809102049y2599a381kf409c0b5f18b6170@mail.gmail.com> <34d269d40809102117g470c4d25l55be7898fc43fc40@mail.gmail.com> <20080911152437.GM6714@it.is.rice.edu> <34d269d40809111951s7bac44cfk5dc9244693dd777c@mail.gmail.com> <20080912142921.GB26773@it.is.rice.edu> <7246.1221445012@sss.pgh.pa.us> 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: 200809/146 X-Sequence-Number: 7161 On Sun, Sep 14, 2008 at 8:16 PM, Tom Lane wrote: > BTW, one thing I noticed was that the hash index build time for the > "wide column" case got a lot worse after applying the patch (from 56 to > 237 sec). The reason for this turned out to be that with the smaller > predicted index size, the code decided not to use the pre-sorting method > that was recently added. Reducing effective_cache_size to less than the > index size brought the time back down, to about 54 sec. So it would > seem that effective_cache_size is too large a cutoff value. I'm > considering changing hashbuild to switch over at shared_buffers instead > of effective_cache_size --- any thoughts about that? Switching to shared_buffers gets my vote, on my test table with 50,000,000 rows it takes about 8 minutes to create an index using the default effective_cache_size. With effective_cache_size set to 6GB (machine has 8GB) its still going an hour later.