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 <mabew...@gmail.com> wrote: > 2010/3/28 Tadipathri Raghu <traghu....@gmail.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 >
