Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CAC3664FCEE for ; Fri, 5 Sep 2008 03:32:22 -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 74679-09 for ; Fri, 5 Sep 2008 03:32:18 -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 1A29A64FCBA for ; Fri, 5 Sep 2008 03:32:17 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so703768fgg.23 for ; Thu, 04 Sep 2008 23:32:16 -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=WXBsgSZlSs075SfEvO/WL0h2lIxjKi2sfWnSu5xr5vo=; b=B75sW3ZorNLlxLMAPjG65jgid25CdatD2AW4FSXfMIY2G5C8lCbGZg/0HRGeoMcCI/ ZgjYn2buWLDzm0V4wgGDHNa15gPih1G7dztxccTyLBBGEgq50SItfdpWB+KXhcvqIja8 VcU1R+TU3bgIRNKlAWC6TAIVLhFdiwuihvsoM= 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=jQ/7bPvPvhxnkuyqxHKwLqrpkdIgi4e0cQgVWHFc4B2CobJgx8WaR6ml0T3z5nGvnx iVKoPaLL1pc/yfeEYHPuUDhKgRuvh7Y66NEYJiIV7Y4C9J5h0ZGfn1UoWyEO0TTKgMzE wh+9xFOHTVXncB+Fxyq/r2tIlchhCzwUZb1cs= Received: by 10.187.172.9 with SMTP id z9mr2534139fao.33.1220596336366; Thu, 04 Sep 2008 23:32:16 -0700 (PDT) Received: by 10.187.225.11 with HTTP; Thu, 4 Sep 2008 23:32:16 -0700 (PDT) Message-ID: <34d269d40809042332l7a1ac5e9t8b1dded2b951dcd2@mail.gmail.com> Date: Fri, 5 Sep 2008 00:32:16 -0600 From: "Alex Hunsaker" To: "Tom Lane" Subject: Re: hash index improving v3 Cc: "Zdenek Kotala" , "Xiao Meng" , pgsql-patches@postgresql.org, "Jonah H. Harris" In-Reply-To: <34d269d40809042048m3b532a5ej7a9588f63a788d0c@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <48BFDA8D.3080506@sun.com> <29987.1220560114@sss.pgh.pa.us> <7627.1220569888@sss.pgh.pa.us> <34d269d40809041742r6a0703dbgfbdd012f7f19de2e@mail.gmail.com> <10995.1220577198@sss.pgh.pa.us> <11525.1220579153@sss.pgh.pa.us> <34d269d40809041854g355a8f68ga3a037a69e3d9fcb@mail.gmail.com> <11962.1220581027@sss.pgh.pa.us> <34d269d40809042048m3b532a5ej7a9588f63a788d0c@mail.gmail.com> 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/52 X-Sequence-Number: 7067 On Thu, Sep 4, 2008 at 9:48 PM, Alex Hunsaker wrote: Ok here are the results: (data generated from the c program before) select count(1) from test_hash; count ----------- 100000011 create index test_hash_num_idx on test_hash using hash (num); CVS: Time: 698065.180 ms patch: Time: 565982.099 ms ./pgbench -c 1 -t 100000 -n -f bench.sql bench.sql select count(1) from test_hash where num = 110034304728896610; CVS: tps = 7232.375875 (excluding connections establishing) patch: tps = 7913.700150 (excluding connections establishing) EXPLAIN ANALYZE select count(1) from test_hash where num = 110034304728896610; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=29.24..29.25 rows=1 width=0) (actual time=0.066..0.067 rows=1 loops=1) -> Index Scan using test_hash_num_idx on test_hash (cost=0.00..29.24 rows=1 width=0) (actual time=0.051..0.054 rows=1 loops=1) Index Cond: (num = 110034304728896610::bigint) Total runtime: 0.153 ms Oddly the index sizes were the same (4096 MB) is that to be expected? Here is the change I made to hashint8 --- a/src/backend/access/hash/hashfunc.c +++ b/src/backend/access/hash/hashfunc.c @@ -61,12 +61,14 @@ hashint8(PG_FUNCTION_ARGS) */ #ifndef INT64_IS_BUSTED int64 val = PG_GETARG_INT64(0); - uint32 lohalf = (uint32) val; +/* uint32 lohalf = (uint32) val; uint32 hihalf = (uint32) (val >> 32); lohalf ^= (val >= 0) ? hihalf : ~hihalf; return hash_uint32(lohalf); +*/ + return val % 4294967296; #else /* here if we can't count on "x >> 32" to work sanely */ return hash_uint32((int32) PG_GETARG_INT64(0));