Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id AEB266330BC for ; Sun, 28 Mar 2010 13:27:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 83794-02 for ; Sun, 28 Mar 2010 16:27:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 3D91063246F for ; Sun, 28 Mar 2010 13:27:43 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o2SGRfrB022619; Sun, 28 Mar 2010 12:27:41 -0400 (EDT) To: Tadipathri Raghu cc: Szymon Guz , pgsql-performance@postgresql.org Subject: Re: Optimizer showing wrong rows in plan In-reply-to: <645d9d71003280033l16069edcp45d9bbb0e7ed5517@mail.gmail.com> References: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com> <645d9d71003280011q6cf2c6d6p7279605e6e0b4235@mail.gmail.com> <645d9d71003280033l16069edcp45d9bbb0e7ed5517@mail.gmail.com> Comments: In-reply-to Tadipathri Raghu message dated "Sun, 28 Mar 2010 13:03:48 +0530" Date: Sun, 28 Mar 2010 12:27:41 -0400 Message-ID: <22618.1269793661@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/357 X-Sequence-Number: 38182 Tadipathri Raghu writes: > Frankly speaking its a newly created table without any operation on it as > you have seen the example. Then how come it showing those many rows where we > have only one in it. Yes. This is intentional: the size estimates for a never-yet-analyzed table are *not* zero. This is because people frequently create and load up a table and then immediately query it without an explicit ANALYZE. The quality of the plans you'd get at that point (especially for joins) would be spectacularly bad if the default assumption were that the table was very small. regards, tom lane