To: "Jon Stewart" <jonathan.l.stewart@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Creating large database of MD5 hash values
References: <c0787d8a0804100748l51b63c7fx34db59e33f13c208@mail.gmail.com>
	<82k5j4sd0z.fsf@mid.bfk.de>
	<c0787d8a0804110828w52a0f614v6130fca72b85956c@mail.gmail.com>
From: Florian Weimer <fweimer@bfk.de>
Date: Fri, 11 Apr 2008 19:04:00 +0200
In-Reply-To: <c0787d8a0804110828w52a0f614v6130fca72b85956c@mail.gmail.com>
	(Jon Stewart's message of "Fri, 11 Apr 2008 11:28:55 -0400")
Message-ID: <82skxsqq67.fsf@mid.bfk.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

* Jon Stewart:

>>  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).

> Can you clarify the "slower to load" point? Where is that pain point
> in the postgres architecture?

COPY FROM needs to read 2.5 bytes on average, instead 2, and a complex
form of double-decoding is necessary.

> Storing the values in binary makes intuitive sense to me since the
> data is twice as dense, thus getting you more bang for the buck on
> comparisons, caching, and streaming reads. I'm not too concerned about
> raw convenience, as there's not going to be a lot of code around my
> application.

The main issue is that you can't use the parameter-providing version
of $sth->execute (or things like $sth->selectarray, $sth->do), you
must use explicit binding by parameter index in order to specify the
type information.

> The idea is that you have named sets of hash values, and hash values
> can be in multiple sets.

The ID step is only going to help you if your sets are very large and
you use certain types of joins, I think.  So it's better to
denormalize in this case (if that's what you were alluding to in your
original post).

> The big operations will be to calculate the unions, intersections, and
> differences between sets. That is, I'll load a new set into the
> database and then see whether it has anything in common with another
> set (probably throw the results into a temp table and then dump it
> out).

In this case, PostgreSQL's in-memory bitmap indices should give you
most of the effect of your hash <-> ID mapping anyway.

> I will also periodically run queries to determine the size of
> the intersection of two sets for all pairs of sets (in order to
> generate some nice graphs).

I think it's very difficult to compute that efficiently, but I haven't
thought much about it.  This type of query might benefit from your
hash <-> ID mapping, however, because the working set is smaller.

--=20
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstra=DFe 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99