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 1egbgK-0003b9-D7 for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Jan 2018 19:35:00 +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 1egbgI-00060A-Ji for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Jan 2018 19:34:58 +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 1egbgI-000600-2j for pgsql-bugs@lists.postgresql.org; Tue, 30 Jan 2018 19:34:58 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1egbgF-0005GX-Jr for pgsql-bugs@postgresql.org; Tue, 30 Jan 2018 19:34:56 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 7A5D8211A1; Tue, 30 Jan 2018 14:34:54 -0500 (EST) Received: from frontend1 ([10.202.2.160]) by compute5.internal (MEProxy); Tue, 30 Jan 2018 14:34:54 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; bh=DFUQjcrOZ5z4Zr2XdeelE4tjEifS8 m43iqF0+cwfRdc=; b=qbC6Ya/8JNadxHNfLx4k9N7skTodJDh7Dr4U9zgoZh2N8 MiiCAElw1UW3k3NzJ8KfKkkgyTxpNn1Z9eg0NT3eUMpAxp2DJGZKR0p0E+U3KDO8 CyUYZzFJbK2VrbQ1jeB1Nvj3OTkXP354IZR2DnFiz4QRnAz2vhejkBijFi6h4HFp IZlFl0GynTVojr8L1Nzt3cE5nXm10F8F7okBvEtix/Y8okZ4mKZIshDJnXAlfx/y dFhgCefWLD02khYCLDLfaJ5s4ruWU53ezAEGlnm5PVjTEBfj1Qzfsml0CiMXTHF9 lm8y1jq9Zplqhlcng8iQy7BeN9Xu+EPzjPgjISuhg== X-ME-Sender: Received: from intern.anarazel.de (unknown [136.24.76.189]) by mail.messagingengine.com (Postfix) with ESMTPA id 2D7F47E0FD; Tue, 30 Jan 2018 14:34:54 -0500 (EST) Date: Tue, 30 Jan 2018 11:34:53 -0800 From: Andres Freund To: "Todd A. Cook" Cc: Tom Lane , Tomas Vondra , Thomas Munro , PostgreSQL Bugs Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an infinite loop Message-ID: <20180130193453.gprzxco2almo5lpk@alap3.anarazel.de> References: <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> <039b8fa5-8272-8b14-3710-926de5844677@blackducksoftware.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <039b8fa5-8272-8b14-3710-926de5844677@blackducksoftware.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2018-01-30 13:57:44 -0500, Todd A. Cook wrote: > 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... FWIW, you could just create a different hash opclass and use it for those queries... Greetings, Andres Freund