Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id EBD7663337B for ; Sun, 28 Mar 2010 14:37:45 -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 94969-04 for ; Sun, 28 Mar 2010 17:37:35 +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 70F29633349 for ; Sun, 28 Mar 2010 14:37:35 -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 o2SHbYEr024447; Sun, 28 Mar 2010 13:37:34 -0400 (EDT) To: Jeremy Harris cc: pgsql-performance@postgresql.org Subject: Re: Optimizer showing wrong rows in plan In-reply-to: <4BAF8D1D.2050109@wizmail.org> References: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com> <645d9d71003280011q6cf2c6d6p7279605e6e0b4235@mail.gmail.com> <645d9d71003280033l16069edcp45d9bbb0e7ed5517@mail.gmail.com> <22618.1269793661@sss.pgh.pa.us> <4BAF8D1D.2050109@wizmail.org> Comments: In-reply-to Jeremy Harris message dated "Sun, 28 Mar 2010 18:08:45 +0100" Date: Sun, 28 Mar 2010 13:37:34 -0400 Message-ID: <24446.1269797854@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.11 tagged_above=-10 required=5 tests=BAYES_05=-1.11 X-Spam-Level: X-Archive-Number: 201003/359 X-Sequence-Number: 38184 Jeremy Harris writes: > On 03/28/2010 05:27 PM, Tom Lane wrote: >> 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. > Does the creation of an index also populate statistics? IIRC, it will set the relpages/reltuples counts (though not any more-complex statistics); but only if the table is found to not be completely empty. Again, this is a behavior designed with common usage patterns in mind, to not set relpages/reltuples to zero on a table that's likely to get populated shortly. regards, tom lane