Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 602CF2E003A for ; Wed, 30 Apr 2008 21:56:35 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 40580-09 for ; Wed, 30 Apr 2008 21:56:19 -0300 (ADT) Received: from mx1.hub.org (mx1.hub.org [200.46.208.106]) by developer.postgresql.org (Postfix) with ESMTP id 5CC1C2E002D for ; Wed, 30 Apr 2008 21:56:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (87-127-95-198.no-dns-yet.enta.net [87.127.95.198]) by mx1.hub.org (Postfix) with ESMTP id 65827B0782A for ; Wed, 30 Apr 2008 21:56:21 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.69) (envelope-from ) id 1JrN3Q-0001Xy-Q3; Wed, 30 Apr 2008 20:53:44 -0400 To: "Tom Lane" Cc: "Jeff Davis" , , , Subject: Re: Understanding histograms In-Reply-To: <26230.1209597464@sss.pgh.pa.us> (Tom Lane's message of "Wed\, 30 Apr 2008 19\:17\:44 -0400") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 5457) References: <4817FC00.50609@cs.pdx.edu> <14251.1209532791@sss.pgh.pa.us> <18628.1209566591@sss.pgh.pa.us> <1209595622.14025.112.camel@dogma.ljc.laika.com> <26230.1209597464@sss.pgh.pa.us> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 30 Apr 2008 20:53:44 -0400 Message-ID: <87skx24zfr.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/475 X-Sequence-Number: 29968 "Tom Lane" writes: > Right. As a matter of policy we never estimate less than one matching > row; and I've seriously considered pushing that up to at least two rows > except when we see that the query condition matches a unique constraint. > You can get really bad join plans from overly-small estimates. This is something that needs some serious thought though. In the case of partitioned tables I've seen someone get badly messed up plans because they had a couple hundred partitions each of which estimated to return 1 row. In fact of course they all returned 0 rows except the correct partition. (This was in a join so no constraint exclusion) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!