Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E5CBC632E24 for ; Sun, 28 Mar 2010 06:18:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 36589-06 for ; Sun, 28 Mar 2010 09:18:28 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from asmtpout012.mac.com (asmtpout012.mac.com [17.148.16.87]) by mail.postgresql.org (Postfix) with ESMTP id CD53C632A07 for ; Sun, 28 Mar 2010 06:18:33 -0300 (ADT) MIME-version: 1.0 Content-type: multipart/alternative; boundary="Boundary_(ID_Vl/7bDkbPxOnk+NNHNk/Vg)" Received: from [192.168.1.33] (195-241-192-241.ip.telfort.nl [195.241.192.241]) by asmtp012.mac.com (Sun Java(tm) System Messaging Server 6.3-8.01 (built Dec 16 2008; 32bit)) with ESMTPSA id <0KZZ00MD6J6SRF40@asmtp012.mac.com> for pgsql-performance@postgresql.org; Sun, 28 Mar 2010 02:18:32 -0700 (PDT) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 ipscore=0 phishscore=0 bulkscore=0 adultscore=0 classifier=spam adjust=0 reason=mlx engine=5.0.0-0908210000 definitions=main-1003280015 Cc: Gary Doades , pgsql-performance@postgresql.org, Szymon Guz Message-id: From: Frank Heikens To: Tadipathri Raghu In-reply-to: <645d9d71003280207p14fa4628i91b51ca8ee954216@mail.gmail.com> Subject: Re: Optimizer showing wrong rows in plan Date: Sun, 28 Mar 2010 11:18:27 +0200 References: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com> <645d9d71003280011q6cf2c6d6p7279605e6e0b4235@mail.gmail.com> <645d9d71003280033l16069edcp45d9bbb0e7ed5517@mail.gmail.com> <4BAF14F2.20200@gpdnet.co.uk> <645d9d71003280207p14fa4628i91b51ca8ee954216@mail.gmail.com> X-Mailer: Apple Mail (2.936) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.854 tagged_above=-10 required=5 tests=AWL=-0.745, BAYES_05=-1.11, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/355 X-Sequence-Number: 38180 --Boundary_(ID_Vl/7bDkbPxOnk+NNHNk/Vg) Content-type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-transfer-encoding: 7BIT Op 28 mrt 2010, om 11:07 heeft Tadipathri Raghu het volgende geschreven: > Hi All, > > I want to give some more light on this by analysing more like this > > 1. In my example I have created a table with one column as > INT( which occupies 4 bytes) > 2. Initially it occupies one page of space on the file that is (8kb). > > So, here is it assuming these many rows may fit in this page. > Clarify me on this Please. See these chapters in the manual: http://www.postgresql.org/docs/8.4/interactive/storage.html The minimum size of a file depends on the block size, by default 8kb: http://www.postgresql.org/docs/8.4/interactive/install-procedure.html Regards, Frank > > Regards > Raghavendra > > > On Sun, Mar 28, 2010 at 2:06 PM, Gary Doades wrote: > On 28/03/2010 8:33 AM, Tadipathri Raghu wrote: >> >> Hi Guz, >> >> It is assuming that there are 2400 rows in this table. Probably >> you've deleted some rows from the table leaving just one. >> >> 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. >> Thanks if we have proper explination on this.. > It's not *showing* any rows at all, it's *guessing* 2400 rows > because you've never analyzed the table. Without any statistics at > all, postgres will use some form of in-built guess for a table that > produces reasonable plans under average conditions. As you've > already seen, once you analyze the table, the guess get's much > better and therefore would give you a more appropriate plan. > > Regards, > Gary. > --Boundary_(ID_Vl/7bDkbPxOnk+NNHNk/Vg) Content-type: text/html; charset=US-ASCII Content-transfer-encoding: quoted-printable
Op 28 mrt 2010, = om 11:07 heeft Tadipathri Raghu het volgende geschreven:

Hi = All,
 
I want to give some more light on this = by analysing more like this
 
1. In my = example I have created a table with one column as INT( which = occupies 4 bytes)
2. Initially it occupies one page of  = space on the file that is (8kb).
 
So, here = is it assuming these many rows may fit in this page. Clarify me on this = Please.


<= /div>
The minimum size of a file depends on the block size, by = default 8kb: http://www.postgresql.org/docs/8.4/interactive/install-procedure.html=

Regards,
Frank


 
Regards
=
Raghavendra

 
On= Sun, Mar 28, 2010 at 2:06 PM, Gary Doades <gpd@gpdnet.co.uk> = wrote:
On 28/03/2010 = 8:33 AM, Tadipathri Raghu wrote:
Hi = Guz,
 
It is assuming that there are 2400 rows in = this table. Probably you've deleted some rows from the table leaving = just one.
 
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.
Thanks if we have proper explination on = this..
It's not *showing* any rows at all, it's = *guessing* 2400 rows because you've never analyzed the table. Without = any statistics at all, postgres will use some form of in-built guess for = a table that produces reasonable plans under average conditions. As = you've already seen, once you analyze the table, the guess get's = much  better and therefore would give you a more appropriate = plan.
=
Regards,
Gary.

<= div apple-content-edited=3D"true">




= --Boundary_(ID_Vl/7bDkbPxOnk+NNHNk/Vg)--