From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: "Todd A. Cook" <tcook@blackducksoftware.com>,
        Tomas Vondra <tomas.vondra@2ndquadrant.com>,
        Thomas Munro <thomas.munro@enterprisedb.com>,
        PostgreSQL Bugs <pgsql-bugs@postgresql.org>
Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an
 infinite loop
In-reply-to: <20171206214003.lvz3q5coaewxr7vp@alap3.anarazel.de>
References: <2a76784c-06cb-46ec-35be-9895d144b839@2ndquadrant.com>
 <263b03b1-3e1c-49ca-165a-8ac6751419c4@2ndquadrant.com>
 <20171206193545.3imvbfwjgrmuho3s@alap3.anarazel.de>
 <ebe8b411-006e-7863-3c60-67ddafe1c636@2ndquadrant.com>
 <790BC80E-D8E5-499B-A1E3-8EE0752B53ED@anarazel.de>
 <06523583-b9c5-1424-ef0a-1fc8063e9ed7@2ndquadrant.com>
 <20171206204616.wcect35fuj5gjde2@alap3.anarazel.de>
 <67e5af9b-f509-7b9e-f7a1-e01112f6c18d@2ndquadrant.com>
 <20171206211225.zcp2crfr3bssn3xr@alap3.anarazel.de>
 <21d507d9-9024-33a8-37c8-b472b3dce7df@blackducksoftware.com>
 <20171206214003.lvz3q5coaewxr7vp@alap3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Wed, 06 Dec 2017 13:40:03 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <16911.1512600919.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 06 Dec 2017 17:55:19 -0500
Message-ID: <16912.1512600919@sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> On 2017-12-06 16:38:18 -0500, Todd A. Cook wrote:
>> I found this problem when I dropped 10.1 into a test environment to see
>> what would happen.  There was no deliberate attempt to break anything.

> Read Thomas' message at: http://archives.postgresql.org/message-id/263b0=
3b1-3e1c-49ca-165a-8ac6751419c4%402ndquadrant.com

I'm confused by Tomas' claim that

>> (essentially hashint8 only ever produces 60% of
>> values from [0,1000000], which likely increases collision rate).

This is directly contradicted by the simple experiments I've done, eg

regression=3D# select count(distinct hashint8(v)) from generate_series(0,1=
000000::int8) v;
 count  =

--------
 999879
(1 row)

regression=3D# select count(distinct hashint8(v) & (1024*1024-1)) from gen=
erate_series(0,1000000::int8) v;
 count  =

--------
 644157
(1 row)

regression=3D# select count(distinct hashint8(v) & (1024*1024-1)) from gen=
erate_series(0,10000000::int8) v;
  count  =

---------
 1048514
(1 row)

It's certainly not perfect, but I'm not observing any major failure to
span the output space.

			regards, tom lane