From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>,
        "Todd A. Cook" <tcook@blackducksoftware.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: <20180129010213.rt3nu73ilff756f6@alap3.anarazel.de>
References: <9cd29b81-7ba1-91a2-f491-9014f4ac87e6@blackducksoftware.com>
 <2a76784c-06cb-46ec-35be-9895d144b839@2ndquadrant.com>
 <219b37b2-d740-d04a-203a-49ae422028f7@blackducksoftware.com>
 <c15378b3-d4aa-a5c0-5538-dd52904dcbc6@2ndquadrant.com>
 <1773c302-2fb1-749f-8ccc-62b23617c5ac@blackducksoftware.com>
 <25629.1512580464@sss.pgh.pa.us>
 <20171206171923.dg7phzfh7nb4lou3@alap3.anarazel.de>
 <7de4b2d6-b119-7d15-04d8-4c0fcb29cbc3@2ndquadrant.com>
 <20180126234537.dyu6jwnqz7snpq7n@alap3.anarazel.de>
 <14184.1517010515@sss.pgh.pa.us>
 <20180129010213.rt3nu73ilff756f6@alap3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Sun, 28 Jan 2018 17:02:13 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <5428.1517250335.1@sss.pgh.pa.us>
Date: Mon, 29 Jan 2018 13:25:35 -0500
Message-ID: <5429.1517250335@sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> Here are two patches that I think we want for 10.2, and the start of one
> that I think we want for master.  0002 is needed because otherwise the
> lack of extra growth leads to noticeably worse performance when filling
> an underestimated a coordinator hash table from the workers - turns out
> our hash combine (and most hash combines) let a lot of clustering
> survive. By adding a final hashing round the bit perturbation is near
> perfect.  The commit messages need to be polished a bit, but other than
> that I think these are reasonable fixes. Plan to push by Monday evening
> at the latest.

The first 2 seem OK to me by eyeball, though I've not done performance
testing.

> The third patch is a version of the random IV discussed in this
> thread. I do think we want to add usage of the extended hash functions,
> as prototyped by Tomas, as that actually helps to fix issues with actual
> hash conflicts. But we additionally need a fallback path for types
> without extended hashtables, and the random IV is a good idea
> nonetheless.  There's no ABI difference in my patch, so I think this is
> actually something we could backpatch. But I don't think it's urgent, so
> I'm not planning to do that for 10.2.  The one thing that could confuse
> people is that it can lead to output order changes from run to run - I
> think that's actually good, nobody should rely on hashagg etc output
> being stable, but it might be a bit much in a stable release?

I disagree: people should reasonably expect the same query and same
data and same plan to give consistent results.  When we stuck in the
"synchronous seqscans" feature, which broke that property, we were very
quickly forced by user complaints to provide a way to shut it off.
I'm also concerned that we'd have to lobotomize a bunch of regression
test cases to ensure stable results there.

IOW, I think randomizing hashkeys is unacceptable for HEAD, let alone
back-patching.

			regards, tom lane