Re: Optimizer showing wrong rows in plan

Hi Guz,

Thank you for the prompt reply.

> 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).

So, whats it assuming here as rows(2400). Could you explain this.

Regards
Raghavendra
On Sun, Mar 28, 2010 at 12:32 PM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:

> 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
>