Re: Optimizer showing wrong rows in plan - Mailing list pgsql-performance
| From | Tadipathri Raghu |
|---|---|
| Subject | Re: Optimizer showing wrong rows in plan |
| Date | |
| Msg-id | 645d9d71003282156n5ec025bbgd49283c14ea968b6@mail.gmail.com Whole thread Raw |
| In response to | Re: Optimizer showing wrong rows in plan (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Optimizer showing wrong rows in plan
|
| List | pgsql-performance |
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)
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)
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)
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)
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@sss.pgh.pa.us> wrote:
Jeremy Harris <jgh@wizmail.org> writes:IIRC, it will set the relpages/reltuples counts (though not any
> 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?
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
pgsql-performance by date: