Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJn4X-0002qS-2i for pgsql-bugs@arkaria.postgresql.org; Tue, 28 Nov 2017 21:05:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJn4W-0007tl-CF for pgsql-bugs@arkaria.postgresql.org; Tue, 28 Nov 2017 21:05:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eJn4W-0007tb-6b for pgsql-bugs@lists.postgresql.org; Tue, 28 Nov 2017 21:05:40 +0000 Received: from mail-dm3nam03on0071.outbound.protection.outlook.com ([104.47.41.71] helo=NAM03-DM3-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eJn4S-0003EB-7m for pgsql-bugs@postgresql.org; Tue, 28 Nov 2017 21:05:39 +0000 Received: from MWHPR1001CA0011.namprd10.prod.outlook.com (10.174.165.24) by BN4PR10MB0868.namprd10.prod.outlook.com (10.164.56.14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256) id 15.20.282.5; Tue, 28 Nov 2017 21:05:29 +0000 Received: from SN1NAM02FT049.eop-nam02.prod.protection.outlook.com (2a01:111:f400:7e44::206) by MWHPR1001CA0011.outlook.office365.com (2603:10b6:301:2a::24) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384) id 15.20.239.5 via Frontend Transport; Tue, 28 Nov 2017 21:05:29 +0000 Authentication-Results: spf=permerror (sender IP is 192.231.134.1) smtp.mailfrom=blackducksoftware.com; 2ndquadrant.com; dkim=none (message not signed) header.d=none;2ndquadrant.com; dmarc=fail action=none header.from=blackducksoftware.com; Received-SPF: PermError (protection.outlook.com: domain of blackducksoftware.com used an invalid SPF mechanism) Received: from ops-mail01.dc2.lan (192.231.134.1) by SN1NAM02FT049.mail.protection.outlook.com (10.152.72.166) with Microsoft SMTP Server id 15.20.239.4 via Frontend Transport; Tue, 28 Nov 2017 21:05:26 +0000 Received: from bds01237.bds-ad.lc (unknown [10.1.6.127]) by ops-mail01.dc2.lan (Postfix) with ESMTPS id 643E03FAB8; Tue, 28 Nov 2017 16:05:26 -0500 (EST) Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an infinite loop To: Andres Freund CC: Tom Lane , Thomas Munro , Tomas Vondra , PostgreSQL Bugs References: <20171127185700.1470.20362@wrigleys.postgresql.org> <7a22f496-92b5-d199-984e-183965918304@blackducksoftware.com> <20171127221041.mezzysc4zcfsqkyc@alap3.anarazel.de> <17357.1511841783@sss.pgh.pa.us> <8ee9458d-1556-1cf4-9cea-ff926a9a99d5@blackducksoftware.com> <20171128181459.d4pqmum2tjtrdwdd@alap3.anarazel.de> From: "Todd A. Cook" Message-ID: <861b9f1f-cdc0-bc49-2595-80bc39c37dc3@blackducksoftware.com> Date: Tue, 28 Nov 2017 16:05:26 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.4.0 MIME-Version: 1.0 In-Reply-To: <20171128181459.d4pqmum2tjtrdwdd@alap3.anarazel.de> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Language: en-US Content-Transfer-Encoding: 7bit X-EOPAttributedMessage: 0 X-Forefront-Antispam-Report: CIP:192.231.134.1;IPV:CAL;CTRY:;EFV:NLI;SFV:NSPM;SFS:(10009020)(6009001)(346002)(376002)(2980300002)(448002)(199003)(189002)(377424004)(24454002)(31696002)(53416004)(5660300001)(65826007)(83506002)(2950100002)(54356999)(50986999)(76176999)(305945005)(230700001)(86362001)(4001150100001)(97736004)(67846002)(6916009)(53546010)(33646002)(64126003)(65956001)(26826003)(50466002)(356003)(47776003)(8676002)(65806001)(81166006)(31686004)(2906002)(81156014)(106466001)(478600001)(53936002)(6266002)(189998001)(6246003)(93886005)(85326001)(36756003)(4326008)(23676004)(104016004)(2486003)(316002)(54906003)(58126008)(229853002)(77096006)(8936002)(217873001);DIR:OUT;SFP:1101;SCL:1;SRVR:BN4PR10MB0868;H:ops-mail01.dc2.lan;FPR:;SPF:PermError;PTR:ErrorRetry;A:1;MX:1;LANG:en; X-Microsoft-Exchange-Diagnostics: 1;SN1NAM02FT049;1:dksX++XenSLsbmKahK6sb2K+Tro3lOyuxWvHRci3zJDkDUcr3eE5JzrJgZWLVQKjS2J/RnVKe43oupkckPi/gQxxMjOweMceOfQnrOGuOMPw7fnN/MsEY75pvMmgGDlw X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id: fc824dd3-a384-4df1-bacd-08d536a3bff7 X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:(5600026)(4604075)(4534020)(4602075)(4627115)(201703031133081)(201702281549075)(2017052603199);SRVR:BN4PR10MB0868; X-Microsoft-Exchange-Diagnostics: 1;BN4PR10MB0868;3:0jcKTors3/2pRqAzzEz7kr4NqNTHfTaFIzR6AbwP4LeA3a5zv/BMqj/0gvlAQYO3+Ov0ZZ95BI55j9CB4UThJTPxtA9O3nDSMnSCLQzsbx1wbByg7nP0sZqWg3Og6wA5Uja6L+YzjM31nNArsBOlajXvpBOP3KEfVKkdSpCZnYcUCzUK8y/bD//zA4xYlAbGY/2/ueWBvqGpQvG3YZL2nm9seVOk9dXBNeqX5PQMI72jTdtYUZidJwMZ2r3pd3r/1j8gL867FA5wBi7jVz1uLB++T06M1vZBWcHMzF7ir8HtTRMmBBldPMZfAEMMfZzqZJnFgl4KNWrM2IEuVEaoyQ==;25:xuKIywtQcrNcHsIaG5XEczUsQauqu4YFMjhcrHS3N4kBKC30viD9zhfMwYxxnFeUennqIcs0DgVMJthpPBNvMfvCk09ZDKvT655F4g8xYX47Lil7mArUF8cKecVmJ8sHhcOAawB4qNKYKSAHFJ/EH/KZi1vAT60DhOYzh+r2Q7dou8tRgcRH+egQljtlQdJh6dBVsUXxVgyets5ks40KE1cY8JuuvhkEjB7yX2sTKWyMO1VX8BwEGPi9TVLy5bcXFfFmM/WoKPAaPrbzPsQlNdCg1aPs6yXgeemuAH9Mr9mtxt/OrP6qWu/Lz0F2PXZ7SzvSI/8w8wVWieJHdXbGug==;31:UyGyHgW2HaokyWzuGAIqCcCVt+IWbHPk5IFchyFf/G1Wk5jl0jqEN945mXDxrpDNvTowNRVHumsRtze3kodfgtzOzsE7t5kya9SA52a8tRXkpk8bwnwxP4KeyCT1o+7RhGmIii4uxsHG1lN48uKs2wsNwh44wXfNGD/fg+ISB6aSxc2StshNe3acfIgpQFWv0MjSQERplv5aCRpaJbTXQ+KN0zcn2gd5JoE8s1Q8/uw= X-MS-TrafficTypeDiagnostic: BN4PR10MB0868: X-Microsoft-Exchange-Diagnostics: 1;BN4PR10MB0868;20://cR0dvmr2u0DcKu2TfVXuyXQHrV3wmhKIkB+dMwjTd5YhAK8sY0dSdoyGY5SdDLDUbfUHA0/Il8gSEB0hOz+83Reib44i3ANP0E239NOQxQzXS5Nx6oquX4w65nadaRV2ycnVZoOTFoi1LHixb18vFFNfyYrDNlnV2ChNicy1CVxl1+pKUQ2FUXNTvQomhkJ/YFtN0D5PY2gCne8txE6jTau9yIFsjXd7f83pIevS97+vVGo+iwKJPzLLIDbQHw8xQp/XINU0WkKWR8ZGgCYtX/UZGfh3Ql6ybEqd9FDtjPI1Nac8dDt6dsjwIA6HKY45q/G3evQ3PJpz3l7Jz7sg+qIw8304pJBmcbom9WDzYThXgK0u2K5aq/XyaQJdLPKFX57FOe7Svcowh4sswhC0DqY82K7D4AR55Zq8e+S8mXyinSJlcvh6QWz+YvWFZvyNPBlFCVRtQg46HE1WZpt0/nMh+d484bNaWa1SnsnJEAHKe3ULHsiipSDjxQFmrW;4:LTQQ/KyMzGHFcKQtvSzRwGQIbh+Yj0hTFMvmAG1hnRtPhC9JAMoO+DJvvNogHaW+snvs4t0ePv/FSwiKPgGlgC1EV/ce1w/7OPEZzJv3+1iK0AInj7/gGv0AmeXfDd3zZf1VNtIFNOzOC9+/utIOlVAgt7lxC45p+/ZkcUAu4iXhF/MzM9KqkR5J1Drr9VkfruA5c/r5mHynUAQt9JpRjNcCxACovS306S+dy5p+bGrPvC859mupHv+X84LQGTAi1BDVxYD3ZvDKlmPcUYfFpQ== X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040450)(2401047)(8121501046)(5005006)(3231022)(10201501046)(93006095)(93001095)(3002001)(6041248)(20161123555025)(201703131423075)(201702281528075)(201703061421075)(201703061406153)(20161123564025)(20161123562025)(20161123558100)(20161123560025)(6072148)(201708071742011);SRVR:BN4PR10MB0868;BCL:0;PCL:0;RULEID:(100000803101)(100110400095);SRVR:BN4PR10MB0868; X-Forefront-PRVS: 0505147DDB X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtCTjRQUjEwTUIwODY4OzIzOjJwWllGc0J6S0lzakcyd21ybzlVUTl6d3dE?= =?utf-8?B?UDZTblVWcWlOdnFRTE1KWlB0UU9oOURTU0J3aGJiaDhBQkRHak5kNTZrZUhq?= =?utf-8?B?NDNqZUgxeXllZXM0cjFNQWFJMVVKS2hKZ1h0ZDdWMUJrQXNVMzdmeG51Rjdm?= =?utf-8?B?cENMaGNxYkdnQTFoazVoT080VFFPM0RqekEwaVJrY1U3MXhnQm5XU0wrSXpj?= =?utf-8?B?UFd0Rk5rSDRmRk8wRC9DbTNPZmNxUUhtYUpkSWppUjMzc3ZXMUdvbnVGT2l6?= =?utf-8?B?UTF5cC9rNDNtQitNQVB2WFlmY0hKalg3OTB3ZURibGtENFBSZUZXaXRXREMy?= =?utf-8?B?T3Evam9zajNOK3IrTkxxTXozVWNPM2xtZ3lrcDdWZ2liN0hlaXZVSjN2eXdZ?= =?utf-8?B?T3Q1V1J4amNCSmdSVnc1RnNTN3dqa0lReG1hS0xPN25jWlpUQmhiV29NOEhj?= =?utf-8?B?K09zZlBVcjNmb21nNjc1V1FGSHhEaXcvZE52WGNIdEtIRG5rSlVIVFhFdktS?= =?utf-8?B?T0RKcnl6MllkL3BVVWpyWVNYMmFwNjBIbGozNVhLb3FBbXFrVThpYnZjZmY4?= =?utf-8?B?MU4rb0ptVVF4cmR3alV3SkpwTDRGNXJ2cm4vZlVpT0ZOWHpybGxBUkhVZmtX?= =?utf-8?B?QUJ5eFhZWll1NzdmcjFyVjJXcGdGVEU4clgrWEphUG1Pc2JSM3lVUHJVNUh0?= =?utf-8?B?VUlQalR6eW02T1RTaHlMcGNwN0R3c2czSUZyT2VrR2FXMmwyam5rOUJSc0pK?= =?utf-8?B?Y2Q5ck0zOWpWZURsT2lZUWJQT3NVV3gydkxpYTJBVGovWFM5SVR2Wi9hTW1E?= =?utf-8?B?djJlK3FBNnlZRlYyd3YzTXhRdk12eklGNkE1dFRPMHB4MHVYTTZPekRVT0w3?= =?utf-8?B?eXVyS2psZnoxMEswVmtySzRjUTF4VFB5YjFYNnI3MEpUSW13ZWlKdDI4Vzhy?= =?utf-8?B?WFFKeXhyUGhhMEpOL1VpQW1TOEdqQTlGLy91UWRFd1dFU1FndkErdE8yR0VQ?= =?utf-8?B?STdMYTJ1WXYyaUkvNlJ1YlVKOXd4UThFeFdLaTk2WjlFbDdwVnhxbXpZbFFJ?= =?utf-8?B?RURUcGttZVRCdG1tQ25YdWRHdTJ3c3NGTVNwTUs4eFV6VFplOHdLVWdBOUt6?= =?utf-8?B?QjkrTjFqTlJNbDZTMnhRSVNYSnM5c0prWFZUZzNHNmpCL3RBL0liTmY3S2VH?= =?utf-8?B?Q0ozenFpTER6VkVMOEoveUN4bW1rTDVwU2VlQ0lCUWxGZHlCaVpSelNkQStR?= =?utf-8?B?cnlxaVdDWnZ3N1NZOTMyMUNKTFFMazRvUExPWFNnNmdsRVBHSHZxMmlZVUJu?= =?utf-8?B?SGE2VGtWQ3FYVk5HUUZCa05XL0hvWnc0elhvNHlJNTM3Ylh2QU9tYm5xemhC?= =?utf-8?B?YVlGR1pvU3ZxM0U1WFkwNVFaSjJwbDM5eTVyR1pxR0FxWmRjbWo2eE9qZm5s?= =?utf-8?B?R2NvWmxhb1Z1WDVvaUlCM3dVbkpBRFRTYjBNd2tTODgvbWkvU0ZjUXJ1ZW9J?= =?utf-8?B?U2NOM1h3TVVtclpiRHR3Q0ppMmhzV2tTcGRibHRTdFBBSE81eXdCeC9DZlJ0?= =?utf-8?B?cEhZdHA1MHBJV0l6dzNPem5SQW5RR1BxQ3NmOUx4aGlRZXpvM0VjOGpVREdB?= =?utf-8?B?N2FmQjhPMGRQRTQ1T2pEN2U4ZkpOL2dBdEJTazRkNHNLUFAxNzZuWnhleUxG?= =?utf-8?B?NHB3eVdJN2Jwd0owanVNQkg5cnVuZ2QycjBqajd6d0xGNjNSU2RyUmE4aE1l?= =?utf-8?B?ZUkrZGpMdE5tc2syZGZ1U1dNKzkxa256dVNpb2phMVZJcFZEeVZtcXFNZDNS?= =?utf-8?B?TlVTRlhpU085cHprU1FYRndaa0cya3RuMnlTaFlHYXBSVkpkNTB5WkxiWFNI?= =?utf-8?Q?P3qRUYx3VHw=3D?= X-Microsoft-Exchange-Diagnostics: 1;BN4PR10MB0868;6:DLSmTp3QQFtW5VsZbzSseY67ig7OPyzmQY3ypFgqVMS1EG28KrXmwbxjRlFbc3YjObzB5a35lJBFczNH97+jsB/lBH1MAsyHWqhyCOzYuOVBQpoGkfYCIy4wH4o91LN5lCDP3Hs05DfBhqt/Gg8KEaURanQmeCe4qG3hGikx85KMZgENobp80jiM3ORf5wYjPfYJ/xvjwWWZhE94ZAAxHPYTnzKcQa+o3o7MdUTEVpkQys7rin6DuqIWQCNi49YaybJcIF0eWf9vHgid7letm7Vwne/zG/Xi3qz+SPV5d1xaI5dcReOMgQ9tDHHl2Pbr5Fw4vtkt+CarxTfTcR2DldcVMT9NjyTrAvynF5Wrmpg=;5:2Ra1EYAQ+oLIgTg7v9itSbHHvxQ00e9hd7b/xlK6zQokfL+xtVFL46YXCaP+1oRumEXF5Xjwv4PkiRRjeAtmI3IrbjJm1GBytWW6twiH52fi3MgFWzpMVzt/L2QbjvVjnirZui+EeNBGdUWwMQ8kq08LdKObp5p4ior2YN8QnFg=;24:Va9zMa3hrH7dkIYETSMikc7qoGsENVJRnfTE2xKskGqWkjvQwC7DU4vxwKPfuClnz90l2kNDBy3AkFs8oeJsGGpbRJjpCOvdnF5xfnsTNzw=;7:VJO5gU6NPPeFtRjRC/YSgqkot1fdO8q22MfLhxHFVzTv/YxIjuHQYdzKk6k/ONVh2QsG3E86yUkebsN+Hn1nl9frbWdBexlcbrU5rzYyx+6j8njN2UfvBLDXle87VSto9ti2kaUbe6h4bAP1E6ZYB0EfHoPdOdbvWYaAOxeV/6VcgF0e/rAiI2+6trUKxSUZesVv5XMoqU4fW4Iqf1KWvJ+DnjWGkDGJs2QN8WuYZYVgRupDhyKJBO58lBO/lksw SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: blackducksoftware.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 28 Nov 2017 21:05:26.8702 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: fc824dd3-a384-4df1-bacd-08d536a3bff7 X-MS-Exchange-CrossTenant-Id: a8cff409-0d9d-48cf-b483-b7442cbeb39d X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=a8cff409-0d9d-48cf-b483-b7442cbeb39d;Ip=[192.231.134.1];Helo=[ops-mail01.dc2.lan] X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN4PR10MB0868 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 11/28/17 13:14, Andres Freund wrote: > On 2017-11-28 09:55:13 -0500, Todd A. Cook wrote: >> On 11/27/17 23:03, Tom Lane wrote: >>> >>> Note that the sample data has a lot of collisions: >>> >>> regression=# select hashint8(val), count(*) from reproducer group by 1 order by 2 desc; >>> hashint8 | count >>> -------------+------- >>> 441526644 | 2337 >>> -1117776826 | 1221 >>> -1202007016 | 935 >>> -2068831050 | 620 >>> 1156644653 | 538 >>> 553783815 | 510 >>> 259780770 | 444 >>> 371047036 | 394 >>> 915722575 | 359 >>> ... etc etc ... >> >> In case it matters, the complete data set will have some outlier values with 10k to 100k >> collisions in this column. > > To make sure we're on the same page, this is data intentionally created > to have a lot of hash collisions, is that right? More or less. It might be more accurate to say that it's created in such a way that we expect to get lots of collisions. -- todd