From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Conley <cheetah@tanabi.org>
cc: pgsql-performance@postgresql.org
Subject: Re: Question regarding fast-hashing in PGSQL
In-reply-to: 
 <CAFbQXuFVR6E0vXgn2Pmz9zZj5wLG56aPg1GptUfW7NtYP6NNSA@mail.gmail.com>
References: 
 <CAFbQXuFVR6E0vXgn2Pmz9zZj5wLG56aPg1GptUfW7NtYP6NNSA@mail.gmail.com>
Comments: In-reply-to Stephen Conley <cheetah@tanabi.org>
	message dated "Wed, 18 Sep 2019 12:41:28 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <9433.1568839177.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 18 Sep 2019 16:39:37 -0400
Message-ID: <9434.1568839177@sss.pgh.pa.us>
Precedence: bulk

Stephen Conley <cheetah@tanabi.org> writes:
> My idea was to hash the string to a bigint, because the likelihood of al=
l 3
> columns colliding is almost 0, and if a duplicate does crop up, it isn't
> the end of the world.

> However, Postgresql doesn't seem to have any 'native' hashing calls that
> result in a bigint.

regression=3D# \df hashtext*
                               List of functions
   Schema   |       Name       | Result data type | Argument data types | =
Type =

------------+------------------+------------------+---------------------+-=
-----
 pg_catalog | hashtext         | integer          | text                | =
func
 pg_catalog | hashtextextended | bigint           | text, bigint        | =
func
(2 rows)

The "extended" hash API has only been there since v11, so you
couldn't rely on it if you need portability to old servers.
But otherwise it seems to respond precisely to your question.

If you do need portability ... does the text string's part of the
hash *really* have to be 64 bits wide?  Why not just concatenate
it with a 32-bit hash of the other fields?

			regards, tom lane