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
Date: Wed, 18 Sep 2019 16:39:37 -0400
Message-ID: <9434.1568839177@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFbQXuFVR6E0vXgn2Pmz9zZj5wLG56aPg1GptUfW7NtYP6NNSA@mail.gmail.com>
References: <CAFbQXuFVR6E0vXgn2Pmz9zZj5wLG56aPg1GptUfW7NtYP6NNSA@mail.gmail.com>
Stephen Conley <cheetah@tanabi.org> writes:
> My idea was to hash the string to a bigint, because the likelihood of all 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=# \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
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cheetah@tanabi.org
Subject: Re: Question regarding fast-hashing in PGSQL
In-Reply-To: <9434.1568839177@sss.pgh.pa.us>
* 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