Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A5D70632A75 for ; Mon, 29 Mar 2010 01:56:44 -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 51490-04 for ; Mon, 29 Mar 2010 04:56:34 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 1D311632A0A for ; Mon, 29 Mar 2010 01:56:34 -0300 (ADT) Received: by vws8 with SMTP id 8so2331484vws.19 for ; Sun, 28 Mar 2010 21:56:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=mNQNZO+tkN/hkRPb3f4vfjEruPI/IJJxgbLOpKceRk4=; b=It6TGVXtzzrHxthUGB7BCHJXyurxK354cTzJeqjy2OAibwCvEwy1GApgMTLAe6UTk3 k5GMdmHXjoy/Txcn2Piyl/zymc2Y0e5RvKk2Z+eTCoBggq0StbgW2CkAYRsrJqAESZKL Q2ltKJW0U+jRNBBfDOrW2wNOYfPTIHs/JawWs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=kWiwCL0AIQksVZLh9FWqKHNUAsFpX3ZfAp+nZGNE1mhjM3zynBbdW8W5dvlTGp9UYf OYc3BsL3tC9vZREjPt7ddyjjRy41PTVEeIQfkhxoKvcG8/k1zo2QbLLeJNaAOsi5apX0 7rv8r73snk4ZLlRgl+ItMiInhxxMZsWsMZPLs= MIME-Version: 1.0 Received: by 10.220.99.197 with HTTP; Sun, 28 Mar 2010 21:56:33 -0700 (PDT) In-Reply-To: <24446.1269797854@sss.pgh.pa.us> References: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com> <645d9d71003280011q6cf2c6d6p7279605e6e0b4235@mail.gmail.com> <645d9d71003280033l16069edcp45d9bbb0e7ed5517@mail.gmail.com> <22618.1269793661@sss.pgh.pa.us> <4BAF8D1D.2050109@wizmail.org> <24446.1269797854@sss.pgh.pa.us> Date: Mon, 29 Mar 2010 10:26:33 +0530 Received: by 10.220.126.222 with SMTP id d30mr2717278vcs.78.1269838593314; Sun, 28 Mar 2010 21:56:33 -0700 (PDT) Message-ID: <645d9d71003282156n5ec025bbgd49283c14ea968b6@mail.gmail.com> Subject: Re: Optimizer showing wrong rows in plan From: Tadipathri Raghu To: Tom Lane Cc: Jeremy Harris , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e68f9f1e46d1950482e95604 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/363 X-Sequence-Number: 38188 --0016e68f9f1e46d1950482e95604 Content-Type: text/plain; charset=ISO-8859-1 Hi Tom, Thank for the update. > 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. > As Harris, asked about creation of index will update the statistics. Yes indexes are updating the statistics, so indexes will analyze the table on the backend and update the statistics too, before it creating the index or after creating the index. Example ====== postgres=# create table test(id int); CREATE TABLE postgres=# insert into test VALUES (1); INSERT 0 1 postgres=# select relname,reltuples,relpages from pg_class where relname='test'; relname | reltuples | relpages ---------+-----------+---------- test | 0 | 0 (1 row) postgres=# create INDEX itest on test (id); CREATE INDEX postgres=# select relname,reltuples,relpages from pg_class where relname='test'; relname | reltuples | relpages ---------+-----------+---------- test | 1 | 1 (1 row) Adding one more thing to this thread ========================== As per the documentation, one page is 8kb, when i create a table with int as one column its 4 bytes. If i insert 2000 rows, it should be in one page only as its 8kb, but its extending vastly as expected. Example shown below, taking the previous example table test with one column. postgres=# insert into test VALUES (generate_series(2,2000)); INSERT 0 1999 postgres=# \dt+ List of relations Schema | Name | Type | Owner | Size | Description ----------+------+-------+----------+-------+------------- edbstore | test | table | postgres | 64 kB | (1 row) postgres=# select count(*) from test ; count ------- 2000 (1 row) Why the its extending so many pages, where it can fit in one page. Is there any particular reason in behaving this type of paging. Thanks for all in advance Regards Raghavendra On Sun, Mar 28, 2010 at 11:07 PM, Tom Lane wrote: > 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 > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --0016e68f9f1e46d1950482e95604 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi Tom,
=A0
Thank for the update.
=A0
IIRC, it will set the relpages/r= eltuples counts (though not any
more-complex statistics); but only if th= e table is found to not be
completely empty. =A0Again, this is a behavior designed with common
usag= e patterns in mind, to not set relpages/reltuples to zero on a
table tha= t's likely to get populated shortly.
As Harris, asked about=A0creation of index will update the statistics.= Yes indexes are updating the statistics, so indexes will analyze the table= on the backend and update the statistics too, before it creating the index= or after creating the index.
=A0
Example
=3D=3D=3D=3D=3D=3D
postgres=3D# create table test(id int);
CREATE TABLE
postgres=3D= # insert into test=A0 VALUES (1);
INSERT 0 1
postgres=3D# select reln= ame,reltuples,relpages from pg_class where relname=3D'test';
=A0= relname | reltuples | relpages
---------+-----------+----------
=A0test=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0= =A0 0 |=A0=A0=A0=A0=A0=A0=A0 0
(1 row)
postgres=3D# create INDEX itest on test (id);
CREATE INDEX
postg= res=3D# select relname,reltuples,relpages from pg_class where relname=3D= 9;test';
=A0relname | reltuples | relpages
---------+-----------+= ----------
=A0test=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0 1 |=A0=A0=A0=A0=A0=A0=A0 1
(1= row)
=A0
Adding one more thing to this thread
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D
As per the documentation, one page is 8kb, when i create a table with = int as one column its 4 bytes. If i insert 2000 rows, it should be in one p= age only as its 8kb, but its extending vastly as expected. Example shown be= low, taking the previous example table test with one column.
=A0
postgres=3D# insert into test VALUES (generate_series(2,2000));
INS= ERT 0 1999
postgres=3D# \dt+
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 List of relations
=A0 Schema=A0 | Name | Type=A0 |=A0= Owner=A0=A0 | Size=A0 | Description
----------+------+-------+---------= -+-------+-------------
=A0edbstore | test | table | postgres | 64 kB |
(1 row)
postgres=3D# select count(*) from test ;
=A0count
-------
=A0= 2000
(1 row)
=A0
Why the its extending so many pages, where it can fit in one page. Is = there any particular reason in behaving this type of paging.
=A0
Thanks for all in advance
=A0
Regards
Raghavendra
=A0
=A0
=A0
On Sun, Mar 28, 2010 at 11:07 PM, Tom Lane <tgl@sss.pgh.pa.us= > wrote:
Jeremy Harris <jgh@= wizmail.org> writes:
> On 03/28/2010 05:27 PM, Tom Lane wrote:=
>> This is intentional: the size estimates for a never-yet-analyz= ed
>> table are *not* zero. =A0This is because people frequently create = and load
>> up a table and then immediately query it without an ex= plicit ANALYZE.

> Does the creation of an index also populate sta= tistics?

IIRC, it will set the relpages/reltuples counts (though not anymore-complex statistics); but only if the table is found to not be
com= pletely empty. =A0Again, this is a behavior designed with common
usage p= atterns in mind, to not set relpages/reltuples to zero on a
table that's likely to get populated shortly.

=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.= org)
To make changes to your subscription:
http://www.pos= tgresql.org/mailpref/pgsql-performance

--0016e68f9f1e46d1950482e95604--