Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJhIJ-0006yA-Gj for pgsql-bugs@arkaria.postgresql.org; Tue, 28 Nov 2017 14:55:31 +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 1eJhII-0006Bm-RT for pgsql-bugs@arkaria.postgresql.org; Tue, 28 Nov 2017 14:55:30 +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 1eJhII-0006Bc-LZ for pgsql-bugs@lists.postgresql.org; Tue, 28 Nov 2017 14:55:30 +0000 Received: from mail-sn1nam01on0074.outbound.protection.outlook.com ([104.47.32.74] helo=NAM01-SN1-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 1eJhI9-0003e5-Ms for pgsql-bugs@postgresql.org; Tue, 28 Nov 2017 14:55:29 +0000 Received: from BN6PR10CA0040.namprd10.prod.outlook.com (10.175.102.154) by MWHPR10MB1888.namprd10.prod.outlook.com (10.175.51.139) 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 14:55:15 +0000 Received: from BL2NAM02FT015.eop-nam02.prod.protection.outlook.com (2a01:111:f400:7e46::202) by BN6PR10CA0040.outlook.office365.com (2603:10b6:404:109::26) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384) id 15.20.260.4 via Frontend Transport; Tue, 28 Nov 2017 14:55:15 +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 BL2NAM02FT015.mail.protection.outlook.com (10.152.77.167) with Microsoft SMTP Server id 15.20.239.4 via Frontend Transport; Tue, 28 Nov 2017 14:55:13 +0000 Received: from bds01237.bds-ad.lc (unknown [10.1.6.127]) by ops-mail01.dc2.lan (Postfix) with ESMTPS id C9A9A3FA94; Tue, 28 Nov 2017 09:55:13 -0500 (EST) Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an infinite loop To: Tom Lane , Thomas Munro CC: Andres Freund , 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> From: "Todd A. Cook" Message-ID: <8ee9458d-1556-1cf4-9cea-ff926a9a99d5@blackducksoftware.com> Date: Tue, 28 Nov 2017 09:55:13 -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: <17357.1511841783@sss.pgh.pa.us> 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)(39830400002)(2980300002)(448002)(189002)(199003)(24454002)(65806001)(65956001)(104016004)(6266002)(31696002)(230700001)(2906002)(47776003)(2486003)(67846002)(23676004)(229853002)(8936002)(77096006)(65826007)(50986999)(54356999)(76176999)(86362001)(53936002)(6246003)(85326001)(189998001)(5660300001)(53546010)(26826003)(316002)(93886005)(478600001)(31686004)(58126008)(4326008)(305945005)(53416004)(64126003)(83506002)(33646002)(36756003)(50466002)(81166006)(81156014)(110136005)(106466001)(97736004)(54906003)(356003)(2950100002)(8676002)(217873001);DIR:OUT;SFP:1101;SCL:1;SRVR:MWHPR10MB1888;H:ops-mail01.dc2.lan;FPR:;SPF:PermError;PTR:ErrorRetry;MX:1;A:1;LANG:en; X-Microsoft-Exchange-Diagnostics: 1;BL2NAM02FT015;1:5/K+AFqv8mAxyGmU2xjWTh7d6zOo2ym+TfKTOivKrJm5tLwpaq/U8McX9C+LNWP30eZUhq1ucfaF7I0kNralRubV2myrBckgEX0v/9vmgwbW/v8fCepN9TWmz7qBZvLH X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id: 6bf8db75-f81b-499b-1166-08d5367007ea X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:(4534020)(4602075)(4627115)(201703031133081)(201702281549075)(5600026)(4604075)(2017052603258);SRVR:MWHPR10MB1888; X-Microsoft-Exchange-Diagnostics: 1;MWHPR10MB1888;3:e5nvhNFEi5fPf/G7eZcsXWloQ7iZTYJj1iVEliKRi5L9OAbp8HKHhL5Zw03zZH68M3ZwJOjaBpJ90tRJdQQK2U5x0djBH886SKxp8cAEVUW7cXWIF+bUD0j1lhSa9TKNSLf8VrfqHtBnmf2MRU3DBvsecs4+bB8WilSuKAvrsMBslLESoeycX9E+fsyAcmSp/3v/U1GcREmgsRUt3ybaGgntf7Go+0QhVYYYxLYcjWCvkecuMewsTy0hRACX+1AdDlbLOWSIlSbagQA0lA9OEJ7EOpzWYZNu0HjBKmLFGZldd0m7t+4RXgsGklZwzxj0G6x2cU05x4U0Nc5I9B5iMw==;25:CzX4LTST+HafYN778mbRQxczxfJgnRSLHtlnL1GBXjKksR7FkkeQzHoAXverdwOIrW77B7yyafTUbqx/WqugEe7Pi3HzLnWegyK2PW8avM55woX0OfIOX07wHyPVfWpCK1RpsG2ywBckomphBNXCgyicS+lotjmH8C54PE9+u5CavuvCtY0XYq+72MXb4jSG9Vp/dxr8/Im7qoJZoQ2QijcGEIMhVQwTeZIGOYT5rtGm66RDfUjolfAfHwVoKvNA4IErd3s6nR+XPvaH+NWLS+BYTmjV9xF/Tm7o3F0pLj/1WOivd0hGnFXUsKrnJuWQxASeO+wOqty2vk8LoUFDhg==;31:bP9VPdVn+viO3RiZZh6CGOLwhH0yB9ZGTxS4nBIGdAeckrThN4rxAVtQU6vX2bnJShuBOnN5FHsaK/scd+A5o97I5WKHb71/Ywd8NXvxgzvn9pXtdPSIp+A2qSHu8V+048flO7mKttyjXuRXPCc4G+XHfPoBi1CS//PvK4oa5pCye07H57EviOTIMCMSymPkb8pX53z9oFiLz62NoTtbbM0zl8BeNv5JThnDCGrpaJ8= X-MS-TrafficTypeDiagnostic: MWHPR10MB1888: X-Microsoft-Exchange-Diagnostics: 1;MWHPR10MB1888;20:+zRyFMj+wAmyg5Bxt04ucisePg3Fiv4wkfn0PPdBz3vBteksj59I892y2n5szrKY+u3c+4dNlAbZQ7J3Uc1ELP0mp8owSCIP4pMZGhnCZe0CIvvP9hWWj0rV2XrTOjq/ahUYTz39gIoZZfM9ldEEdNMAlD6ts2psz3qcX5DA4P7uKgU8LqJg8EMU7sHeJlPzKalulPY0eb4AOpBtt2ksbMAsiRsn32x/Hac3d4S3eoTe7RphexPQW45Lr5wNn65b9iCNSh7aA+K8ildm9/IS3hOvoppQtOjeZY3XWeEYtdPJGU2tQC+i6fUoCzChCUUwSsbDF9LE9WpFeUDwiHQEUkegbRGn80QU9txpBdt7m/wuvuNAQOoY9nZk+Yr5GYQEjng+WfhKaxZfeZ4+Nk1u4gIlm7RtvCz1GJwrRcc6ocjCxqUBbiuAvsQenrUyPqakU2RpyRIc1aMhjYS9SgxI7T9+Ju26VS2CJ8Urjit7GG7ulwiP2YFdqsg6e7wdRQJk;4:VTJJDhseQRGRzliXg+5+YkIZb2U6mJDMlkiJ7bPfrctoFj+6C3cHFLY6yvdQI9Fh7xO9cWuLdBh7Z+3lKTxCQS7M/zgVHB72r/e6QAwCtZ8suSfn4efShR6KKoSe0thRsFXzn/wu7rAlQ6etEeMYBt3Kf+iR5afGFQMy0mPaJeUB76Ke0HRvbOFCMfNj1aREsMQEBotb4BICZwYo8cF+Ex5Co+NVlcqrlRWP/a6Bynqyx6YRVQvg/oWa7pZP7hzpAcExz+RizXSqd/uswzbpuw== X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040450)(2401047)(5005006)(8121501046)(3231022)(10201501046)(93006095)(93001095)(3002001)(6041248)(20161123564025)(20161123562025)(20161123558100)(20161123555025)(20161123560025)(201703131423075)(201702281528075)(201703061421075)(201703061406153)(6072148)(201708071742011);SRVR:MWHPR10MB1888;BCL:0;PCL:0;RULEID:(100000803101)(100110400095);SRVR:MWHPR10MB1888; X-Forefront-PRVS: 0505147DDB X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtNV0hQUjEwTUIxODg4OzIzOmdzWXlYUjJpeGN6K09uTGVQSS9HTGhJMG45?= =?utf-8?B?cHRCR2RHREZGQnI5bFpSNDlrZStwV2VLZy9yYmg4MDB2elZ1M3d6UkpyYmQ5?= =?utf-8?B?SXYvbHNobnNOS2lyMWpkeHRUNk5tcEpqT0g0amFBQmNueXVDci9WUjY3VGc5?= =?utf-8?B?QkpteEU4TXBNU2ZRK1A1clpscE80Rk5kN2hXbDc2b3VLUENRUlJDYjZxdE0r?= =?utf-8?B?ZnFLdmV1VEhEK2IwTUp4dnNqTXJ1QlJZRmpsaGRiS0tkWWlGeGFBaFljUmJG?= =?utf-8?B?d2g3ZjUrdERSMW45TjMzazdmdjkxeHJNNTdXVHdoT1V1S25HNE80bms3TzE1?= =?utf-8?B?c04wNkFQZ0NZWnRYZVlZeUNEYyt3VExlU2VQUVovSmR0UmE0OTQ4Rk9yUWF2?= =?utf-8?B?cjZRVzJLckVrODVJZDd4ckxQcTFidkR2QkpoYTdJUHg3VHJJN0RKeENKbFBO?= =?utf-8?B?TC80TUsxYWdaVmNQVEJ0eHJ2WUYzeHA3NDB1RnVPa0JxajRaT1dPOGUva0hz?= =?utf-8?B?Z0hrM1JuWSs2dTRaMHY0ZnBQNkViSWhLcHlFMWhaUzNGc0RPeFdjY255cHR6?= =?utf-8?B?a25ZeStPUzQ1S2d5MHlXUXJBWjU0KzdiOFhrYkJ3WVptSjlyTmRRR0ZzVFhx?= =?utf-8?B?aEt5UlNUdUhnM3lEVyswclRCWDdXM21zSUI1VjRCN0NuSk5OZ3ZzVmt1VFV4?= =?utf-8?B?bS9rVUR2azh4QUtmSkZNdFZvTm5kSUhwUWdMK0dEQ0wxNEFOcjd0VVhncnho?= =?utf-8?B?em9DUmJDNlR5RDZKS21QSlJYeXJNNWNkSS9HMExTTHJkeFc2c3U5dUI1UFFQ?= =?utf-8?B?ZnZBdGZOaUNrOXpCY3hYdDZNall1NUJjUlBiZHc4Skh5RkZIUHI2eHlEaU9Q?= =?utf-8?B?T3U2cllSOGtZZE9qak1YTVhtSTV4U29sRHl5N1NHL1dzNEFvcFhtaVc1T0pv?= =?utf-8?B?a3YyY1BGQTdXZm03TTJaOHo2dGI5MEZwRFgvdUhWVlROck9wd1F4WUUzRmVy?= =?utf-8?B?RmpZN25yME1WRGNxanZJYXFSdVVMcVhaMlczNkhnQmJtbVRZZHI2Y3BxbDhv?= =?utf-8?B?QWdUdE9OOE1rNGhZNmQwRng3VFJINWo1YnoyOHo0MjJlZzVYb1RwSVl2S1dp?= =?utf-8?B?YW84d2JFVkJld1ZZczhLVGlBSWl3czZCK1hzL2xvKzFodG00WjUvRmpyMDJV?= =?utf-8?B?SjcyWkkrOUtrczJOSXdEQ0w2a0c1V1d1WmxjRzNkeUhQekF5bDEvQUNsMEI4?= =?utf-8?B?TUppSGUvRHFhVXczYmN2WFJmR1BNaW11dWNudWc1VHZwT1hDTFFIcWpsRGtQ?= =?utf-8?B?UW0yQnYvUmt2RGpOVldNRXdycGdpUjRsZjhSUWxTakZ5V3lrMk43SGxyb1Bw?= =?utf-8?B?OCs4Vzh1c1Nkdk5QaTh1SGNrajZlYmFtUnZGWXBjdHlxN1lNWkVQN3pEd3RL?= =?utf-8?B?K0ZmN295SEU0aURleDVqaFhBakk4dUlpOUpSMFdlakhZdGlWL1VvRFNCdGZr?= =?utf-8?B?NFdkT0hIQ2c2UUhDRFpoZXlHMkdqVXJBQWFUWU9PRVN1OVlwSVpWN1pzSFpn?= =?utf-8?B?OTdaQWZJYjhLTGRDMTlqUkYzLzhTQ09iS1RYVDZnWEFTSXhubGJ0ekppK0w2?= =?utf-8?B?MXhXSDkvMEJ4TDFlT1ZabWlTU1RwQTA1TFVLZEdhMG1KQ2xLdWdsc2ZXZlNG?= =?utf-8?B?ZjY5U3N1STdZUGpoWFc0b1ZCUUZFUGdQTkdhT2NNRWNvZzFRSzRrazdraWcy?= =?utf-8?B?ZCtYYzFJdXp1WEZaUCtYaVdEdlBCcTRRT0IyeDkvN0E2NThCeWY1ZUxhWDBS?= =?utf-8?B?dnJlVEM4U3JwU0RCMTdQZ2Z5YXJQZTdlTkRzQWN3cHFEblE9PQ==?= X-Microsoft-Exchange-Diagnostics: 1;MWHPR10MB1888;6:olgHXjS766nJ0zHfPqjIeUYrOONyO/Bgmcw5e6WrGq0mosMl0ce/GcDB55BHDu/fuBJeYRbCmmSSdfm5Zo0+wtc6hxNb9GDCVjJqUR4lnry+wFElOixKQ2+xQ3Y+f7QR5swlfhIVYCYwwBbOE8HlDq+XL7kPZLlo+LZl5AzaUSsVTokzP4PsND1saNFQGPe4NjX4M8GQKifsA9y2YPjBTAnySUl5mMrh1gXs+lUnjuy0TqYsqzGd2Wj/SJmSxY1iydE1Umffevdz3Q6Im4uPm3eMvvlzuEiBbQzWRN9vNzys0Rkp8DHSYe+xf0NiwizLcQTg2nAzWeVjGHDZbsexQ0oPhu2HdnsmGlGMM+prVng=;5:57JxjtHmnTz1Jkn16s7z1JrHLPFuOdEiFMr9Mc9IOwNBAXiEHGwPKiFTDvvwOKJ5QhhLmOsEQWYVwDs+kHS04WOR3KqosNONkyt+0iRfZ1teVhm89jGAJP6RWo/HrrHJN9GHDz1TLC5f9NyrgWKlCXmfxkx5mWYbI5lLmvAK6iQ=;24:d8+MQdRwzRN+9p9kHNxrDYJOBXdE7N8/JNUj8UpdWrjMexnqKwDETMoRDvqNLpLvgpPsYX7a4dF9cishLU9NLgbBqlSA0g4sgGz26ytvWgw=;7:ZFpWdOlcWTdMuPdtXD/B4s5EHs/EMiiRf0SFLYXcm6amJQbQsGeIxP/kcCXfUx4gW5+V/J20qqlSaiEzCP5r/N28JNbpVc0mUNPUlkZM/RcFLiAyQbKz4TKj/ivUPPWkimmTOjFHbafppCfDSAJVMnShxX9ZzYuNM3hTGm7V/QdGyc8QIVBahbFDkjuGpgIeV2q1LF0VRFV6bM/hptdAj8fydLAoKtzZoc5ksY77TWG/hPrPhk6Dw0wWNEl3SinW SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: blackducksoftware.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 28 Nov 2017 14:55:13.9029 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: 6bf8db75-f81b-499b-1166-08d5367007ea 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: MWHPR10MB1888 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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. -- todd