Re: Optimizer showing wrong rows in plan

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeremy Harris <jgh(at)wizmail(dot)org> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>