Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id BB3F32E003D for ; Fri, 11 Apr 2008 11:13:51 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64213-03 for ; Fri, 11 Apr 2008 11:13:32 -0300 (ADT) X-Greylist: delayed 00:08:29.473933 by SQLgrey-1.7.5 Received: from mx01.bfk.de (mx01.bfk.de [193.227.124.2]) by developer.postgresql.org (Postfix) with ESMTP id 127EC2E0032 for ; Fri, 11 Apr 2008 11:13:32 -0300 (ADT) Received: from mx00.int.bfk.de ([10.119.110.2]) by mx01.bfk.de with esmtps (TLS-1.0:RSA_AES_256_CBC_SHA1:32) id 1JkJs9-0000dP-Ip; Fri, 11 Apr 2008 16:04:57 +0200 Received: from fweimer by bfk.de with local id 1JkJsC-0002JS-7H; Fri, 11 Apr 2008 16:05:00 +0200 To: "Jon Stewart" Cc: pgsql-performance@postgresql.org Subject: Re: Creating large database of MD5 hash values References: From: Florian Weimer Date: Fri, 11 Apr 2008 16:05:00 +0200 In-Reply-To: (Jon Stewart's message of "Thu, 10 Apr 2008 10:48:59 -0400") Message-ID: <82k5j4sd0z.fsf@mid.bfk.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/151 X-Sequence-Number: 29644 * Jon Stewart: > 1. Which datatype should I use to represent the hash value? UUIDs are > also 16 bytes... BYTEA is slower to load and a bit inconvenient to use from DBI, but occupies less space on disk than TEXT or VARCHAR in hex form (17 vs 33 bytes with PostgreSQL 8.3). > 2. Does it make sense to denormalize the hash set relationships? That depends entirely on your application. > 3. Should I index? Depends. B-tree is generally faster than Hash, even for randomly distributed keys (like the output of a hash function). > 4. What other data structure options would it make sense for me to > choose? See 2. In general, hashing is bad because it destroy locality. But in some cases, there is no other choice. --=20 Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstra=DFe 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99