Re: Optimizer showing wrong rows in plan - Mailing list pgsql-performance

2010/3/28 Tadipathri Raghu <traghu.dba@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