From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
cc: "Todd A. Cook" <tcook@blackducksoftware.com>, pgsql-bugs@postgresql.org,
        Andres Freund <andres@anarazel.de>
Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an
 infinite loop
In-reply-to: <b337cd3c-7091-2342-8e66-36919d91f70c@2ndquadrant.com>
References: <20171127185700.1470.20362@wrigleys.postgresql.org>
 <fd924fd9-8f27-d945-04a9-57b503896107@2ndquadrant.com>
 <5d764e7f-269f-7f6d-3885-88295dc9e03d@blackducksoftware.com>
 <b337cd3c-7091-2342-8e66-36919d91f70c@2ndquadrant.com>
Comments: In-reply-to Tomas Vondra <tomas.vondra@2ndquadrant.com>
	message dated "Sat, 27 Jan 2018 00:11:40 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <12510.1517008946.1@sss.pgh.pa.us>
Date: Fri, 26 Jan 2018 18:22:26 -0500
Message-ID: <12511.1517008946@sss.pgh.pa.us>
Precedence: bulk

Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> I suspect you're right the hash is biased to lohalf bits, as you wrote
> in the 19/12 message.

I don't see any bias in what it's doing, which is basically xoring the
two halves and hashing the result.  It's possible though that Todd's
data set contains values in which corresponding bits of the high and
low halves are correlated somehow, in which case the xor would produce
a lot of cancellation and a relatively small number of distinct outputs.

If we weren't bound by backwards compatibility, we could consider changing
to logic more like "if the value is within the int4 range, apply int4hash,
otherwise hash all 8 bytes normally".  But I don't see how we can change
that now that hash indexes are first-class citizens.

In any case, we still need a fix for the behavior that the hash table size
is blown out by lots of collisions, because that can happen no matter what
the hash function is.  Andres seems to have dropped the ball on doing
something about that.

			regards, tom lane