Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Uq9i6-0007BQ-Ic for pgsql-performance@arkaria.postgresql.org; Fri, 21 Jun 2013 22:21:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Uq9i5-0006ZQ-Ny for pgsql-performance@arkaria.postgresql.org; Fri, 21 Jun 2013 22:21:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Uq9i4-0006ZK-V4 for pgsql-performance@postgresql.org; Fri, 21 Jun 2013 22:21:37 +0000 Received: from smtp.01.com ([199.36.142.181]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Uq9i0-0000ah-AI for pgsql-performance@postgresql.org; Fri, 21 Jun 2013 22:21:36 +0000 Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 8279D294173; Fri, 21 Jun 2013 17:21:30 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id ssm29QKW2l8s; Fri, 21 Jun 2013 17:21:30 -0500 (CDT) Received: from smtp.01.com (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 63B44294177; Fri, 21 Jun 2013 17:21:30 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 30989294176; Fri, 21 Jun 2013 17:21:30 -0500 (CDT) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id oFPdzLqTOMad; Fri, 21 Jun 2013 17:21:30 -0500 (CDT) Received: from [172.47.23.103] (70-36-143-10.dsl.dynamic.sonic.net [70.36.143.10]) by smtp-out-1.01.com (Postfix) with ESMTPSA id AF547294173; Fri, 21 Jun 2013 17:21:29 -0500 (CDT) Message-ID: <51C4D1E8.6090602@agliodbs.com> Date: Fri, 21 Jun 2013 15:21:28 -0700 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130510 Thunderbird/17.0.6 MIME-Version: 1.0 To: Tom Lane CC: "pgsql-performance@postgresql.org" Subject: Re: Weird, bad 0.5% selectivity estimate for a column equal to itself References: <51C4AEE5.8040704@agliodbs.com> <25930.1371850378@sss.pgh.pa.us> In-Reply-To: X-Enigmail-Version: 1.5.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 06/21/2013 02:32 PM, Tom Lane wrote: > Josh Berkus writes: >> I'm getting something really odd in 9.2.4, where the planner estimates >> that the selectivity of a column equal to itself is always exactly 0.5% >> (i.e. 0.005X). I can't figure out where this constant is coming from, >> or why it's being applied. > > See DEFAULT_EQ_SEL. Why is it using that? We have statistics on the column. What reason would it have for using a default estimate? > But why exactly do you care? Surely it's a stupid > query and you should fix it. (a) that test case is a substantial simplication of a much more complex query, one which exhibits actual execution time issues because of this selectivity bug. (b) that query is also auto-generated by external software, so "just fix it" isn't as easy as it sounds. (c) PostgreSQL ought to perform well even on the stupid queries. Obviously, we're going to code around this for the existing software, but this is an example of a planner bug which should be on the fix list. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance