Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1egb9o-0000x1-PO for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Jan 2018 19:01:24 +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 1egb9o-00038P-1T for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Jan 2018 19:01:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1egb6U-00064p-O4 for pgsql-bugs@lists.postgresql.org; Tue, 30 Jan 2018 18:57:58 +0000 Received: from mail-bn3nam01on0046.outbound.protection.outlook.com ([104.47.33.46] helo=NAM01-BN3-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1egb6N-0004U1-8k for pgsql-bugs@postgresql.org; Tue, 30 Jan 2018 18:57:57 +0000 Received: from BN4PR10CA0001.namprd10.prod.outlook.com (10.164.240.11) by BLUPR10MB0625.namprd10.prod.outlook.com (10.163.124.151) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256) id 15.20.444.14; Tue, 30 Jan 2018 18:57:45 +0000 Received: from SN1NAM02FT057.eop-nam02.prod.protection.outlook.com (2a01:111:f400:7e44::203) by BN4PR10CA0001.outlook.office365.com (2603:10b6:403::11) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384) id 15.20.444.14 via Frontend Transport; Tue, 30 Jan 2018 18:57:45 +0000 Received-SPF: TempError (protection.outlook.com: error in processing during lookup of blackducksoftware.com: DNS Timeout) Received: from ops-mail01.dc2.lan (192.231.134.1) by SN1NAM02FT057.mail.protection.outlook.com (10.152.73.105) with Microsoft SMTP Server id 15.20.444.13 via Frontend Transport; Tue, 30 Jan 2018 18:57:44 +0000 Received: from bds01237.bds-ad.lc (bds01237.bds-ad.lc [10.1.6.127]) by ops-mail01.dc2.lan (Postfix) with ESMTPS id 32DA6406A4; Tue, 30 Jan 2018 13:57:44 -0500 (EST) Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an infinite loop To: Andres Freund CC: Tom Lane , Tomas Vondra , Thomas Munro , PostgreSQL Bugs References: <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> <15ae5ae2-bc74-ebef-f9d6-34b16423cc04@blackducksoftware.com> <20180129183118.rdtm34fshwxlajzm@alap3.anarazel.de> <633f3730-0cba-9aed-2724-3ae4295bb0b2@blackducksoftware.com> <20180129192647.a6ni3jslsq5uzjkz@alap3.anarazel.de> From: "Todd A. Cook" Message-ID: <039b8fa5-8272-8b14-3710-926de5844677@blackducksoftware.com> Date: Tue, 30 Jan 2018 13:57:44 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.5.2 MIME-Version: 1.0 In-Reply-To: <20180129192647.a6ni3jslsq5uzjkz@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)(376002)(396003)(346002)(39380400002)(39850400004)(2980300002)(377424004)(199004)(189003)(6916009)(106466001)(65826007)(104016004)(5660300001)(336011)(186003)(77096007)(36756003)(53416004)(8676002)(93886005)(86362001)(31696002)(31686004)(50466002)(2906002)(85326001)(81156014)(47776003)(6246003)(305945005)(81166006)(64126003)(65806001)(2486003)(229853002)(26005)(67846002)(8936002)(58126008)(53936002)(356003)(53546011)(65956001)(23676004)(4326008)(316002)(230700001)(26826003)(83506002)(2950100002)(6266002)(54906003)(76176011)(97736004)(6346003)(478600001)(217873001);DIR:OUT;SFP:1101;SCL:1;SRVR:BLUPR10MB0625;H:ops-mail01.dc2.lan;FPR:;SPF:TempError;PTR:ErrorRetry;A:1;MX:1;LANG:en; X-Microsoft-Exchange-Diagnostics: 1;SN1NAM02FT057;1:veeui779sxk600uNcUekeC+A6iD5imsVhp6dLlxIR7XU0W68fKl2gDHy0T3eRPR7wJBPjjEBwjKv8ihhWvRMvZtpHiyaTYKgi4n/Iwm6vJ+0fgiZwsPVB+DOM/HozkEc X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id: 9506fe86-b27e-499f-1caf-08d5681358eb X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:(7020095)(4652020)(4534165)(4627221)(201703031133081)(201702281549075)(5600026)(4604075)(2017052603307)(7153060);SRVR:BLUPR10MB0625; X-Microsoft-Exchange-Diagnostics: 1;BLUPR10MB0625;3:Hnaxy4MZQNygG/3M8vM9wTHtVkS1uizoq/xz1EThDxOY3ObkrfUeJ+Z476lOH/1Pk3my9TfEQUy2+NXyCn4qXhMt2+CPBXJ0YIuWoSWJ6ecpwbi8YDfY76cMi04Nzuz9AiM//1Yp1aCpn6vZ3b25QholdZqs7j71GDiF0VeW6iQdVJKhKVizT+HpIrRKIL6fDM6lvCFdNpoCd7YeGjzJs+vknmozCW/sbiC7HPeCALieMM9ch/txqgxlmZ3ecu8HRMMePpr2m38yJGa8l1/A6P6lJHRTLHfbzD3fDXs/8Ydb8C/Uq5tOMRzUC3agKRUu2ObWVDBb/6Pufgd0m7MFzQ==;25:uPQkU65AyChzCxDCMHJyk/0QdhCMw7uKYDBlSLXTMASbgdl1GlR4xSPqceHwurRqjXZag5Gg5CH6iHWEu4ncnCVosnZ3MVKPBwqZHf+lIRga/8GOpBAMQTfHcw+sfGRDS00gQQ21Nlnf+QrAtttq4sl50na3ym+nLzyoaPnOzbdpkvvMqsP5zBp8suBb+thLgXyBMFA5y/j8PrpnBsny+EM/aj71Rxh/Y1L2AzsxHRV1NU4K4MSVxxpAATzNOQ95AmzJBH7J3k8k38Cay+jSgsi2UUkY9/jGh/sPMHX5cbWq4NRpFdO7UtKDBPHIvWzTub1AzMqImZRtiPMKFS/sJg==;31:HhjHAMA+26LPLw0ZezCdJYFCsZ33a1wG/Sd3EACvTawjUVGUinzzZZKr2GzxoyMJiST7U4CrgKB85Ayx4i2EaIV3wdXxNNBaNngb2B5IMUu1+t1i9p0BKqrhVDBc09iMya7/2WaZ896I52vTLnXCdLhQEm9hh2S5nXbcDA5oYo3uWPjcgF3xa4O1vLczRS494oWYfcfoWX5vkG6ICOwaHSoR7UXiXcGxIRaHbQNDpB4= X-MS-TrafficTypeDiagnostic: BLUPR10MB0625: X-Microsoft-Exchange-Diagnostics: 1;BLUPR10MB0625;20:rDY1E9hVdq3hzx8dV6jDE6yvVTHrqMNoZCEkCbVSAxC5dIl7ySowN1Y4YvNU5txV21CX3we5/JpNwnbSkrTbrSMXEoCIv2ePh8mqJNc2uItFx8FJigzPwc4XUS2k/JRxIbWNsQC84M6shPANw+uHW6Jroj2zcx22avp31EgT6W7uqsix7dNOrnA1OY8WWGGSL+Zl7qHNW+f8EFjbD2DgukO4p+rV/1zUjiXMcR4IcWp4EdXs1m90IaA3xmmR0smbSBqnf+K4gLs/WaW65Owni25CMrgJUCe4FlaQYUDgGVIM3ZrRFVjr3yuxILC8jVZVFPt5D4iIo4govyootgwCDFqov+MkHxumvoM/uicEC4Pvxwzug8uleZvkmKW8ZtNJcFTSS/OcISHIffPleuxq1frxjgNYiDRHFVB2wGzC5TvfJBDLpBB8wq2VqnTd4DadFCuCNNr0GQ2cfTosOrq3yDSlTYL2GLy0pmRCvbntsZiNFQISEvESg90AThDfE2O/;4:5ZNCPknEkzrswtf4ws1IpfJpoxPDO/rQ9Ooj6OLEILQZA6FTuhgGYrJWA6ZdOM6Oxv5GaO8tdf+ATMTxQotjfHcKGMBoKp4RCWc95iqbUdayvBLGolwRvnDtayb0oTH2BajjD5daziLoELpDH+oSv7XGLmtnugjDaUsZPKWIKkO4oQzAjAqcCdIbTRSTDlWR3I91zxr3WcypeYpsFyHqiSRXGcFkVzfinw6Tkk44jyP7S0wVFDveKEnP5ysma51f0xSoCoJ6bkcoubBoBO3Isw== X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040501)(2401047)(8121501046)(5005006)(93006095)(93001095)(3002001)(10201501046)(3231101)(944501161)(6041288)(20161123564045)(201703131423095)(201702281528075)(20161123555045)(201703061421075)(201703061406153)(20161123560045)(20161123558120)(20161123562045)(6072148)(201708071742011);SRVR:BLUPR10MB0625;BCL:0;PCL:0;RULEID:;SRVR:BLUPR10MB0625; X-Forefront-PRVS: 0568F32D91 X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtCTFVQUjEwTUIwNjI1OzIzOlNGYWZCN20wQXdpa0FuSUI0Tmp2enFLR0NO?= =?utf-8?B?NTFaZDNhNm9nY0RqR0dvZXpJQU00WjVFdDRTQ0FHbWZEaVRXa21Oam5vcnZO?= =?utf-8?B?ZVNVK2kxQVFQZVE4RU1zNUVZT09tbnNoZW5PNnFod3l2N09iNUhBUE1YUjZK?= =?utf-8?B?eVFIdFpXTCsxSkhPbjZNdUk4ZXEyYVJrM0tabG1RU05ZQm5aeVo2RHZ4R2N5?= =?utf-8?B?TFpFKzVCWWdQZDJIMG5TQ0l1TU0wVXNaWUs2Y28zWmRZenAzaGtjL1VReEcx?= =?utf-8?B?SVdHQ0VERUxrbVd6ZEsvanNQSVB4OGZYdDRKM2RldVFJN1ZIRklDb2RZYU92?= =?utf-8?B?YXM4MWozWExrMTd5Si9nSE8yWm9FS3BLcjBoamRNdm1WcUhLcDJxTU9najZW?= =?utf-8?B?clRnL0ZzZVowOGlZR04yQWhRSjZFc0JVVHF4YUpKdENYZXo5Vmd1WEJDcWVq?= =?utf-8?B?VXBaOXlTWmhEMHdOVG1qNFZ1Q1pSUVp1aFJrVTNGbVNzSlNnSHBBeUV3cTJw?= =?utf-8?B?SWo0Si9jUzJjQlAxS3lIRGE2QlA0cEVIOExqYUFTalpwZXNZamFLbWRGWlNs?= =?utf-8?B?bkJzbUF0eVpzZFExNGtnVjVKc0R4RnNTcjdiM1ZQNHVJNUlvZXFmd3JxcVZG?= =?utf-8?B?MS9uQkJoRWt1dGd2c2ZrNzFtK0d0cm1sODMzeUZVMnVDVjU4SitaNnBKL2Rq?= =?utf-8?B?MkNDQlNEM1VxcmhIdUpGOFkydVY3UVpnRU43b0lzQWM4OFRMUHg2d05maGNE?= =?utf-8?B?TDFPc1Jydmk4eHJ4SGtYR1Axa05kVVBjNDBMUVAvLzlWVmJmVFFHUXcxbld0?= =?utf-8?B?OW1DcVRHZW5hSW1LVFE3N1BjSVBiZVJmRkVIT0NEUXdKRU9SNXVIZE10SnNI?= =?utf-8?B?TFo1c0VDZEg5VTVTd2J2VmxXZmZ1TVN0N2Rxd3QrQUdGWUYycFNBUlhQQUdC?= =?utf-8?B?YXhsUllZMWtyY2JkYlBvS2VQK1NDV3dZVnJDbWJsZE9QMHF2SXc0bXpOUG9p?= =?utf-8?B?Y2gyNkVnNzkzbkxQd2w2dVUzZmFWeW5NT0NFcGxCTjkrNEpiTVVvckZvWEJ0?= =?utf-8?B?MXdBa0g1dHhIdnNDTnRJbG5TK2U4UGc1WlRFT3hscmlScitFWTFlMVhBeTI2?= =?utf-8?B?QmhrZ3pybGlIN0MyZjVRM09CVDZZQjdORDRqRnRBdjNib3QybnJjSThXUDBz?= =?utf-8?B?YTRTbVc0d0ZiVlk4S0c1Tm9RSk5FZ0lzcit2WTBEeVRrOHU3alRKWVN2UE9Y?= =?utf-8?B?YmxqSHpFdWNYek10bTU3VGh2eHdwQXRCN2lCR1JpZ3RPY0xReWRkZ05qOHVW?= =?utf-8?B?MHFmOVR0TlA5VFc5S2ZPSGVaM2UxTU1CazFaZnI0dnA3UDFDYW1FQ3lyekxk?= =?utf-8?B?YXh0eW1MK0VuQmJEZ25SZHBUMUsySzBpczd2dU5hL3VuYlJwczVrd1l5N2Nz?= =?utf-8?B?amVDRDFoQXFvbTZBN29QWEFGUXp6MXhnT0diOE1RY24rMGFacjRyVnhKeCty?= =?utf-8?B?NVpaMjZkVElnQUJCdXY0NXlkdlg2aWN3UWVDZDhvdllGdk9Zbks3UUtzQWdX?= =?utf-8?B?dEtSR2dGV3M2RFJFaURRT1dIMTVjRXVoQXg3WUdFQXhLWDdNVnZNOXhMV0hW?= =?utf-8?B?YWdyVVRxSHZ2OTlRbXFibm5rbjlyRWZhZUpqUEhqZXlOWmFjNkFoWTJFVEpY?= =?utf-8?B?TStsQnYwRlZBOGhiaUdvY3dIVXRrTmFWcERUanJQT0YyV2t6ck1GblZxTTRI?= =?utf-8?B?NGdjTStyL1ptaFg4amNHaHZmRHpZMHloRitKLy90WUFIMm9TaHRIWmx4REVH?= =?utf-8?Q?hujohuYWPTnTC?= X-Microsoft-Exchange-Diagnostics: 1;BLUPR10MB0625;6:BbFX0x9ISJx3vguYRkjVS+svTL2x1P9Az6RrK8LJWbsWAYr4QD4qXPGBUEtxErwDMhYdxTnuDmVH4rd2LDEOIlRyh+DHGF9QL42sZPNmBlEI5ir9LtTKVdK6LE8s6Nfgfgy0VDBx4ipjzEScLnu1BfnsT+YnFmgyem5EGtrjOnUul6VV2imaljL1rWeOV26DyqqsSv8txy4o6gUEZ7hi/nH2kYsVf8ssRcq1OHzXl6eF7sYmEInblpecVUpLCQigb9VyPC5B8zVNg4VUyfgGIhF54wqQUxpX6DRmMCZSDa+1B81uS24hh2gsL2uLnnPPBRR30eoB0GxEjW1Makm69JDqIlgkpQijcCjfmMad4aQ=;5:7IPFv3TuVH7dkzm3KsZiT1U1T2RN9yQ8Iey1jIYUjBYtaf5kAlFiQv2bwcXaEDDr9K1ztKnIo++Cxf6kOcZg3A6biJi3oCcs+AiJRDg07awtFokwxsNAY3wK4NUvQn0NtaERcnZYfQZSvisYOdzNtg7MkOAZuq/pb5qMEp7hYkY=;24:ACwCKTlxnC8uGuQO4SQEPGqVHurrcTVXABzIblzwnabZ9rjhaUAUu5M1j4O8xYjDuRIft1UipwM8HMRY/xYcb7ilf+vBZ+ezvn85MWOLwxM=;7:J/nmGo4xkF9NEV8SX/xaIV51V6AaFhtkZmxEoB1/wUVjKh2nPOBq4QyJzsBqvxC9SEYbwOn0WhSCArif6R0e75mgVBsGRmHHTDW24PzsQZNwgyt+N5fQsCyvnv7S5E0iV/GPh3kRYhMWQieJw6/NaZcxzWIALCJ7ovpVuKo3JaWmI1K3PXJjUYBOtpQr3BiNeDY+n799CrF24Cns5T50RXa3PHIGUR8V4exFS21XNJ7nKO2X3DRG5DenENEsUuBj SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: blackducksoftware.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 30 Jan 2018 18:57:44.5920 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: 9506fe86-b27e-499f-1caf-08d5681358eb 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: BLUPR10MB0625 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 01/29/18 14:26, Andres Freund wrote: > On 2018-01-29 13:56:37 -0500, Todd A. Cook wrote: >> With those patches applied, I'm able to process a 175 million row data set >> without any problems. > > Cool! > >> I'll try the 900 million row set next. We're also able to process those 900 million rows without problems. :) It took about 28 minutes (average of 3 runs). Out of curiosity, I then modified hashint8() as previously described. With that change, run time dropped to 11 minutes (also average of 3 runs). FWIW, the data values in these sets are sort-of random (where I can't explain the "sort-of" in a public forum), but strongly biased towards negative infinity. Starting again from scratch, we could probably remove the bias, but we have 28-30 billion of these things collected over the last 14 years (starting in PostgreSQL 7.4) and it's kinda tough to change directions at this point... -- todd