Re: Optimizer showing wrong rows in plan

2010/3/28 Tadipathri Raghu <traghu(dot)dba(at)gmail(dot)com>

> Hi All,
>
> Example on optimizer
> ===============
> postgres=# create table test(id int);
> CREATE TABLE
> postgres=# insert into test VALUES (1);
> INSERT 0 1
> postgres=# select * from test;
> id
> ----
> 1
> (1 row)
> postgres=# explain select * from test;
> QUERY PLAN
> --------------------------------------------------------
> Seq Scan on test (cost=0.00..34.00 *rows=2400* width=4)
> (1 row)
> In the above, example the optimizer is retreiving those many rows where
> there is only one row in that table. If i analyze am geting one row.
>

No, the optimizer is not retrieving anything, it just assumes that there are
2400 rows because that is the number of rows that exists in the statictics
for this table. The optimizer just tries to find the best plan and to
optimize the query plan for execution taking into consideration all
information that can be found for this table (it also looks in the
statistics information about rows from this table).

>
> postgres=# ANALYZE test;
> ANALYZE
> postgres=# explain select * from test;
> QUERY PLAN
> ----------------------------------------------------
> Seq Scan on test (cost=0.00..1.01 *rows=1* width=4)
> (1 row)
>
> My question here is, what it retreiving as rows when there is no such. One
> more thing, if i wont do analyze and run the explain plan for three or more
> times, then catalogs getting updated automatically and resulting the correct
> row as 1.
>
>

Now ANALYZE changed the statistics for this table and now the planner knows
that there is just one row. In the background there can work autovacuum so
it changes rows automatically (the autovacuum work characteristic depends on
the settings for the database).

> Q2. Does explain , will update the catalogs automatically.
>
>

No, explain doesn't update table's statistics.

regards
Szymon Guz