From: Alex Hunsaker <badalex@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Zdenek Kotala <Zdenek.Kotala@sun.com>
Cc: Xiao Meng <mx.cogito@gmail.com>
Cc: pgsql-patches@postgresql.org, "Jonah H. Harris" <jonah.harris@gmail.com>
Subject: Re: hash index improving v3
Date: Fri, 5 Sep 2008 00:32:16 -0600
Message-ID: <34d269d40809042332l7a1ac5e9t8b1dded2b951dcd2@mail.gmail.com> (raw)
In-Reply-To: <34d269d40809042048m3b532a5ej7a9588f63a788d0c@mail.gmail.com>
References: <ded849dd0808171846g2c6c65adub942bd2510a6c94f@mail.gmail.com>
<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>
On Thu, Sep 4, 2008 at 9:48 PM, Alex Hunsaker <badalex@gmail.com> 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
inline patch 1: src/backend/access/hash/hashfunc.c (+3/-1 lines) download inline-1.patch--- 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));
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: badalex@gmail.com, tgl@sss.pgh.pa.us, Zdenek.Kotala@sun.com, mx.cogito@gmail.com, jonah.harris@gmail.com
Subject: Re: hash index improving v3
In-Reply-To: <34d269d40809042332l7a1ac5e9t8b1dded2b951dcd2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox